Knowledge Drop

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

  • 6 April 2021
  • 8 replies
  • 696 views

Userlevel 4

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.                

 

 

 


8 replies

@Department of Customer Love  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

 

Userlevel 3

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

@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?

 

 

Userlevel 5
Badge

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.

 

 

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

 

 

This should produce a ranking like this:

 

@sam  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… :)

Userlevel 5
Badge

@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”.

Userlevel 3

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_visualizations

@sam and @molly.lippsett Your last suggestions are pretty similar--thanks, I will do that!

Reply