Hi All,
I am wondering if there any way to filter on row_number window function in Looker or using LookML. I am using snowflake database and natively, it has QUALIFY clause to filter.
I am creating row_number with partition and need only row_number = 1. Can someone please guide me if there any equivalent function available in Looker?
Thank you.
Hi Vinay,
Use derived table in LookML and write your query which you are running in Snowflake .
Example :
derived_table:
{
sql: SELECT * from ( SELECT *, row_number() over(partition by <partition column> order by <column name> ) as row_num from <Table_name>
) where row_num=1
}
or use dimension row_num as filter in explore/visualisations
derived_table:
{
SELECT *, row_number() over(partition by <partition column> order by <column name> ) as row_num from <Table_name>
}
Thank you @adnanjmi for you reply. Is there any other way without using derived table query?
did you get any solution of this? I too need to use rank and then qualify to get latest data from my BQ table.
~Ashish