How do I do a ROW_NUMBER OVER (PARTITION BY ...) with a table calc?

Knowledge Drop

Last Tested: Jul 1, 2018

match(${products.brand},${products.brand})

if(${partition}=offset(${partition},-1) , 1+row()-${partition} , 1

 

This content is subject to limited support.                

Comments
JWorkman
Explorer

@LookerExperts  How is this meant to be implemented? In a table calc? I have the below syntax in my table calc and I’m getting syntax error
 

match(${fact_activity_log_composite.workouts_started},${fact_activity_log_composite.workouts_started})

if(${fact_activity_log_composite.activity_date}=offset(${fact_activity_log_composite.activity_date},-1) , 1+row()-${fact_activity_log_composite.activity_date} , 1

Hey @JWorkman what’s the syntax error? I just counted and there may be a paranthesis missing at the end...but yes, the title indicates it’s for a table calc

JWorkman
Explorer

@molly_lippsett You are right, there was a missing parenthesis… but I am actually still getting the error.  It’s just saying “Expression Incomplete”. Do I need some kind of comma in between these two expressions? Or, how are these two functions interacting with each other?

 

aa4ff48a-7da6-4f76-93ff-63f3bff65992.png
sam8
Staff

I believe it’s meant to be 2 table calcs. The first is the definition of a calc called “partition”.  For this you enter the dimension you want to partition by.

ace6ecb5-4461-4c3b-8a19-feeba653959c.png

Then, you write the second calc which gets the rank inside each partition.

85b9e829-bd2c-423c-96e5-f08cf6b141a6.png

This should produce a ranking like this:

86482749-841f-4901-956b-088823d2d614.png
JWorkman
Explorer

@sam8  Thanks for this reply, it works perfectly…. I just need to now figure out a workaround for using that Table Calc as a filter now… 🙂

sam8
Staff

@JWorkman what kind of filter are you trying to do?

If you only wanted to show, say, the top 5 of each rank, then you could make another table calc that returns “true” if the rank is <=5, and then click “hide nos from visualization”.

You can create an additional table calc that resolves to “No” if the condition isn’t met, and then “Hide No’s from Visualization” for the table calc column, would that work? There’s an example in docs here: https://docs.looker.com/exploring-data/using-table-calculations#using_table_calculations_in_visualiz...

JWorkman
Explorer

@sam8 and @molly_lippsett Your last suggestions are pretty similar--thanks, I will do that!

Loretta
New Member

cancelled

Version history
Last update:
‎04-05-2021 03:14 PM
Updated by: