Refer to Priority Flag if Column item is not unique

[gustavo2]

Hello,
I would like to filter the data in Looker based on the price per city.

For example: the result should be City A, City B, City C and City D and their prices.
The challenge I have been facing is that City A and City D are not unique. for that it should refer to the Priority Table Column, where price 1.50 € has priority for City A and therefore the 1€ for City A should not be displayed (filtered out).

Cities with single values should be shown regardless.

Is there a dimension or something in LookML I am missing?

I don’t seem to be able to use the OR filter effectively or to filter our the Priority Table ‘No’ altogether as it would remove City B and City C.

Any thoughts?

[3Easy]

You might get somewhere with Table Calculations, i.e.there are a range of functions that allow you to compare values in a list, and you might build some logic around that, e.g. finding second instances of a City and deciding if that row is in or out based on the count of that value coupled with the yes/no in the Priority Table. This approach is similar to Excel logic, and your image looks like Excel so I assume some familiarity with this kind of formulaic approach. I suspect lateral thinking and table calc could get you there…

if(offset(${table_view.column_field},1)=${table_view.column_field},1,0)

[gustavo2]

You might get somewhere with Table Calculations, i.e.there are a range of functions that allow you to compare values in a list, and you might build some logic around that, e.g. finding second instances of a City and deciding if that row is in or out based on the count of that value coupled with the yes/no in the Priority Table. This approach is similar to Excel logic, and your image looks like Excel so I assume some familiarity with this kind of formulaic approach. I suspect lateral thinking and table calc could get you there…

if(offset(${table_view.column_field},1)=${table_view.column_field},1,0)

@3Easy , thank you for this.
I found a logic that works for the time being. It is a bit frail as it depends on the data being sorted in Ascending Order in 2 columns (City and Priority Table).

My logic was to create 2 separate table calculations.

1. Is_Next_Row_Priority

Concatenate the Priority Table of the current row with the following row. (it works because it is sorted and there are only 2 occurrences per City max)

CONCAT(${table_view.priority_table},offset(${table_view.priority_table},1))

2. Priority Check
   Checks whether the next row is priority (in case of yes, it means it is a different City as the Column City is sorted Alphabetically). If current row and the following are ‘No’s then they should be visible (meaning they are different cities), the only case in which they should be hidden is ‘NoYes’ meaning that current row is not priority but the next one is. Therefore, current row should not be visible.

   IF(${Is_Next_Row_Priority}= "NoNo", yes,if(${Is_Next_Row_Priority}= "YesNo", yes,no))

   and to wrap it up I hide the No’s from visualization.
   
   I will leave it as above for now, but looking for ways to do the same check if the number of occurrences in the city row is greater than 2.