Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Knowledge Drop

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

  • 6 April 2021
  • 9 replies
  • 7182 views
Department of Customer Love
Rank
Userlevel 5

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.                

 

 

 

9 replies

JWorkman

@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

 

molly.lippsett
Rank
Userlevel 4

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

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

 

 

sam
Rank
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:

 

JWorkman

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

sam
Rank
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”.

molly.lippsett
Rank
Userlevel 4

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

JWorkman

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

Loretta

 

cancelled

Reply


Terms of Service