Display a duplicate through LOOKUP

(Malaka Jayawardene) #1

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.

(Rosemary Black) #2

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.

1 Like
(Steve Coile) #3

The TL;DR response is use ANY(SELECT(...)) instead of LOOKUP().

1 Like