Qualify in LookML

[vinay_kumar_gup]

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. 

[adnanjmi]

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>

}

[vinay_kumar_gup]

Thank you @adnanjmi for you reply. Is there any other way without using derived table query?

[a_shah]

Hi @vinay_kumar_gup 

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