Hi all, I don’t know how to concisely put my query here, so here goes (Sorry for the lengthy description)!
There’s a table called “Surgery Logbook” in my app. In it, there is a set of columns called [Surgery Title], [Specify - 1], [Specify - 2], [Specify - 3], which fetch data from a table called “Surgery List”. So basically they work as dependent dropdowns. When the user picks a surgery title from [Surgery Title] dropdown, then either [Specify - 1], [Specify - 2] or [Specify - 3] will appear for further specification of that surgery depending on the dropdown pattern created in the “Surgery List”. So this “Surgery Logbook” table has a lot of data pertaining to each surgery user inputs.
Now what I want is to create a few charts using the data in the “Surgery Logbook”. I created a separate table called “Analyser” for this purpose where the user gets to select which kind of chart/comparison of data he/she wants (I have created an Enum column with different choices ranging from Chart 1 to Chart 7, each chart comparing a certain variable taken from the “Surgery Logbook” table’s data, i.e: Surgery vs Complication Rate, Surgery vs Age Distribution etc., and when the user selects one of the Chart options, a specific chart gets created, and the user gets to decide which surgery titles that chart will contain).
So one of the charts I wanted to create was to compare surgeries and their complication rates. But I wanted the user to select which surgeries are to be compared. So I created a form for that inside the “Analyser” where the user gets to input a list of surgeries. Since the best way to create a list of surgeries is by creating a ‘reference’ table, I created a table called “Multiple Surgeries” where I again created columns called [Surgery Title], [Specify - 1], [Specify - 2], [Specify - 3] fetching the same dropdown data from the “Surgery List” table as I did for the “Surgery Logbook”, so this way, the user can really specify the surgeries he/she wants to compare. (eg: If surgery title is ‘Anterior Resection’, the [Specify - 1] column allows to further specify it as ‘Laparoscopically done’ or ‘done as open surgery’). Then I created a slice for the “Surgery Logbook”, with a Row Filter Expression to filter and show only the data pertaining to the Surgery Titles and specifications user selects via that “Multiple Surgeries” reference table using a LOOKUP expression. This works perfectly for me!
Only issue is, if I want to compare the same surgery’s 2 different arms (for example, Anterior resection surgery’s laparoscopy arm vs open arm), although user can create 2 rows in the “Multiple Surgeries” table, 1st row with [Surgery Title]=Anterior Resection and [Specify - 1]=“Laparoscopic”, and 2nd row with [Surgery Title]=Anterior Resection and [Specify - 1]=“Open”, and even if the LOOKUP expressions have been written to fetch Row 1 and Row 2 data separately in to the Slice for the Row Filter Expression, it still combines both rows together and show as a single entity - Anterior Resection in the chart, and not as 2 different Anterior Resections. Is there a way to mitigate this issue? Everything works perfectly as long as [Surgery Title] brings up different surgery titles, the problem comes only when the same surgery title’s further specifications are different.
You could use the unique row key as the lookup instead of the title, or concatenate the title and specifics to get a unique result.
The TL;DR response is use ANY(SELECT(...))
instead of LOOKUP().
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |