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?
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)
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.
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))
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.