Skip to main content

Hi Team.

 

Recently, I came across this situation where I need to Provide Rank based on some Numeric field Values.

I have accomplished this logic using the Gainsight Rules Engine. And below are the steps to create Rank.

 

First of all, the flow which I am going to show you that helps, if your data set has duplicate values or distinct values as well.

Example, my data set is:

And the output is like below

 

In order to get that required output below are the steps that we have to follow in Rules Engine.

 

Note: the steps which I am going to explain, will be the same for both types of data (Distinct or Duplicate) 

 

Step 1: Fetch the Data set with the Numeric field(in my case it’s ARR) on which we wanted to apply Rank.

Based on the above sample example data the output of the step 1 is 

Step 2: Create a Transform task in which pull the numeric field(ARR) in to the Group By Section and drag any other field(Account ID or Name) in to Show and apply count on it.

So the output of this task is

 

Step 3: Create one more Transformation task and using Case Expression create one field for the Joining Purpose. And provide “1” if the Count of Account ID != Null else provide “0”

Screenshot:

then the result set would be like

 

Repeat the same steps 1, 2, and 3

So that finally will get 2 sets like below

So totally we have created 6 Steps till now.

 

Step 7: Join the Step 3 and Step 6 with the help of “Join Purpose” field and the join type should be “Retain common records from both datasets” or “Retain all records from left dataset”.

So now the data set would be like

 

 

Step 8: Create a Transformation task, and compare the “ARR” and “ARR from the Set 2” field using Case Expression.

if ARR >= ARR from the Set 2 then select ARR from the Set 2 else provide “0”

Screenshot for this calculation:

So now the data set would be like

Step 9: Create one more Transformation task and here calculate the Count of “Case Expression Comparison” field group by “ARR from the Set 2” field. 

In filter condition use “Case Expression Comparison != 0”

Screenshot:

So now the data set would be like

 

 

Step 10: Merge the Step 9 with Step 1 with the help of ARR and ARR from the Set 2 fields. And get the Account ID, Name, ARR fields from step 1 and choose “Count of Case Expression Comparison” field (and rename as Rank) from the Step 9.

The join type should be “Retain common records from both datasets” or “Retain all records from left dataset”.

 

So now the data set would be like

 

Finally, the rule flow is 

 

Thanks.

This is a great tutorial, Thanks Phani for sharing this with community. I dont think it should be this difficult to achieve ranking and will see from product side if we can simplify this usecase.

Thanks @rakesh. Yes, currently assigning the Ranks is a huge process. If we have any function to calculate the rank in our product that would be great and it saves lot of time.

What about macros for standard functions such as ranking so that they don’t need to be reinvented?

Macros can be highly optimized and opaque like calculated fields.

I’m trying to use this workaround to rank based on a date field. I’m getting stuck on Step 8 since you can’t have a case expression output be a date. Is there a way to workaround this to rank based on date?

I’m trying to use this workaround to rank based on a date field. I’m getting stuck on Step 8 since you can’t have a case expression output be a date. Is there a way to workaround this to rank based on date?

 

I found a shorter solution that provided very similar results.

Step1: Create a Transform task with a ‘dummy’ column equal to 1. Include the GSID and the field you want to Rank

Step 2: Create a second Transform task with a ‘dummy’ column equal to 1

Step 3: Merge on the ‘dummy’ columns with keep all from left data set. ADD A MERGE FILTER to compare the ranking fields and only keep records where your field is Greater or Equal.

Step 4: Group by GSID and get the count of Records.

Duplicates are still a problem. If two or more are equal, they will both have the lowest rank. So if the top 5 are all tied, it will appear is no ranks 1 through 4

@rakesh any update on the rank functionality?

Reply


Terms & ConditionsCookie settings