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.
