Question

Rank

  • 3 December 2020
  • 4 replies
  • 159 views

 

Hi,

I would like to calculate the rank in LookMl (i don't want to use table calculation) of my dimension according to the different measures as in the table below.

Thank you for your answers

 


4 replies

Hi,

 

Please refer the Point/Section 3 - ‘Top N Items Versus the Rest of the Population’ in this URL to implement the Top N/Rank according to your requirement. 

 

https://help.looker.com/hc/en-us/articles/360023573273-Great-Use-Cases-for-Parameter-Fields

 

Also share how did you tweaked according to your example.

Userlevel 3
Badge

Hi @tatuspark,

 

Another common way to do this, is to use a SQL derived table or a native derived table to add a window function. This way we can use a rank and partition by function and create a dimension for rank. I have listed an example below for both a native derived table a SQL derived table. The SQL syntax used in this example is Snowflake

 

####NDT Version:
view: NDT {
derived_table: {
explore_source: order_items {
column: user_id {}
column: delivered_date {}
derived_column: test {
sql: rank() over (partition by user_id order by delivered_date);;
}

}
}
dimension: user_id {
type: number
}
dimension: delivered_date {
type: date
}
dimension: test {
type: number

}

####SQL Derived Table Version
SELECT
order_items."USER_ID" AS "order_items.user_id",
TO_CHAR(TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST(order_items."DELIVERED_AT" AS TIMESTAMP_NTZ))), 'YYYY-MM-DD') AS "order_items.delivered_date",
rank() over (partition by "order_items.user_id" order by "order_items.delivered_date") as rank
FROM "PUBLIC"."ORDER_ITEMS"
AS order_items


GROUP BY 1,TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST(order_items."DELIVERED_AT" AS TIMESTAMP_NTZ)))
ORDER BY 1 DESC
LIMIT 500

Please let me know if you have any questions!

 

Thanks,

Eric

@Eric_Lyons  Thanks to your answer.

 

What is the difference between Native Derived Table and SQL Derived Table ?
In which case do we use one or the other?

Thanks

Userlevel 3
Badge

Hi @tatuspark,

 

A Native derived table is defined in LookML, while a SQL derived table is defined in SQL. I would say it generally depends on your preference. You use an existing explore as the base for an NDT. With a SQL derived table you use a SQL query to create the derived table. 

 

This article talks about some of the features of both options: https://docs.looker.com/data-modeling/learning-lookml/derived-tables#simple_example

 

Thanks,

Eric

Reply