Calculating Rank using existed field value in Rules

  • 29 April 2022
  • 2 replies
  • 73 views

Userlevel 7
Badge +3

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.


2 replies

Userlevel 6
Badge

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.

Userlevel 7
Badge +3

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.

Reply