First of all I want to thank few people here before I go over this document

- Massimo Grossi - He is the brains behind this (Simple Rank() question)
- Thirumala Dandamudi - Shared the link for the post so that I can learn about this new technique (How to do this in the script)
- John Jackson - It was John's question which invoked Thirumala to provide the link to Massimo's post. Without his post, I would have never got the chance to learn.

I am going to share a slightly modified version of John's data used in his post (How to do this in the script) just to save myself from creating a new sample

Table: LOAD * INLINE [ Group, Vendor, ID, Trans_ID Black, UVW, A65, A165 Black, WXY, A66, A166 Black, WXY, A67, A167 Black, WXY, A69, A169 Black, WXY, A70, A170 Black, WXY, A71, A171 Black, XYZ, A72, A172 Black, ZAB, A73, A173 Black, ZAB, A74, A174 Black, ZAB, A75, A175 Black, ZAB, A76, A176 Blue, QRS, A56, A156 Blue, QRS, A57, A157 Blue, QRS, A58, A158 Blue, RST, A59, A159 Blue, RST, A60, A160 Blue, RST, A61, A161 Blue, RST, A62, A162 Blue, RST, A63, A163 Green, KLM, A37, A137 Green, KLM, A38, A138 Green, LMN, A39, A139 Green, LMN, A40, A140 Green, LMN, A43, A143 Green, LMN, A44, A144 Green, LMN, A45, A145 Green, LMN, A46, A146 Green, NOP, A47, A147 Green, NOP, A48, A148 Green, NOP, A49, A149 Green, NOP, A50, A150 Green, OPQ, A51, A151 Green, OPQ, A52, A152 Green, OPQ, A53, A153 Green, OPQ, A54, A154 Green, OPQ, A55, A155 Red, DEF, A22, A122 Red, DEF, A23, A123 Red, DEF, A24, A124 Red, DEF, A25, A125 Red, DEF, A26, A126 Red, DEF, A27, A127 Red, FGH, A28, A128 Red, GHJ, A29, A129 Red, GHJ, A30, A130 Red, GHJ, A31, A131 Red, GHJ, A32, A132 Red, GHJ, A33, A133 Red, IJK, A34, A134 White, TUV, A64, A164 Yellow, ABC, A1, A101 Yellow, ABC, A10, A110 Yellow, ABC, A11, A111 Yellow, ABC, A12, A112 Yellow, ABC, A2, A102 Yellow, ABC, A3, A103 Yellow, ABC, A4, A104 Yellow, MYM, A98, A141 Yellow, MYM, A99, A142 Yellow, MYM, A100, A143 Yellow, MYM, A101, A144 Yellow, ABC, A5, A105 Yellow, ABCC, A13, A113 Yellow, YOY, A90, A122 Yellow, YOY, A91, A123 Yellow, YOY, A92, A124 Yellow, YOY, A93, A125 Yellow, YOY, A94, A126 Yellow, YOY, A95, A127 Yellow, YOY, A77, A201 Yellow, ABCC, A78, A202 Yellow, ABCC, A79, A203 Yellow, ABDD, A80, A204 Yellow, ABDD, A81, A205 Yellow, ABDD, A82, A206 Yellow, ABDD, A83, A207 Yellow, ABEE, A15, A115 Yellow, CDE, A16, A116 Yellow, CDE, A17, A117 Yellow, CDE, A18, A118 Yellow, CDE, A19, A119 Yellow, CDE, A20, A120 Yellow, CDE, A21, A121 ];

The requirement is to find the Rank of Count(ID) here and it needs to be continuous (as shown below)

Group | Vendor | Count(ID) | Rank Needed |
---|---|---|---|

Yellow | ABC | 8 | 1 |

Yellow | YOY | 7 | 2 |

Yellow | CDE | 6 | 3 |

Yellow | MYM | 4 | 4 |

Yellow | ABDD | 4 | 4 |

Yellow | ABCC | 3 | 5 |

Yellow | ABEE | 1 | 6 |

But this output isn't possible using a simple Rank function (even with different combinations available within Rank function)

Rank(1,0) -> Num(Rank(Count(ID), 1, 0))

Rank(2,0) -> Num(Rank(Count(ID), 2, 0))

Rank(3,0) -> Num(Rank(Count(ID), 3, 0))

Rank(4,0) -> Num(Rank(Count(ID), 4, 0))

So in order to achieve "Rank Needed" column, we can use a work around provided by Massimo

**Div(Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1),Group, Vendor), '00000'), '-', -Aggr(Count(ID), Group, Vendor)), Num(Rank(Count(ID),1,1), '00000')), 6) + 1**

But how does this formula work?

**Step 1-1**

**Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1),Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor))**

Concate a distinct (**USING DISTINCT**) list of possible ranks by each group (**TOTAL <Group>**) which is sorted in ascending order using the expression **-Aggr(Count(ID), Group, Vendor)** and separated by '-'.

Assign the possible list of rank a particular formatting here where they should all be 4 digit. I think the assumption here is that the max rank would ever be 9999, if this is not true, then you can change from '0000' to '00000'. This will now cover upto 99999 (this is where the division will come into play, which I will go into later)

**Step 1-2**

**Num(Rank(Count(ID),1,1),'0000')**

Find the rank for each row and assign it the same number formatting as the formatting assigned with the Concat function. '0000' or '00000'.

**Step 2**

**Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1), Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor)), ****Num(Rank(Count(ID),1,1),'0000'))**

Find the location of '-' within the concatenated list of rank, but this index function has a third parameter telling it to look for which '-' in the list. The third parameter is basically the rank from Step 1-2 which goes like 1, 2, 3. So basically what is the location of first 1st '-', 2nd '-' and so on....

**Step 3**

**Div(Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1), Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor)), ****Num(Rank(Count(ID),1,1), '0000')), 5) + 1**

I don't think we have to use Div function here, I believe that if the list of rank is small you can also use Pick match, but for a larger rank, div will work better here.

The idea is that since we have used '0000' as number formatting the location of '-' will change by 5 each time (2, 7, 12, 17, 22, 27, 32, ......) so dividing this by 5 will give us (0, 1, 2, 3, 4, 5, 6) and adding one to this will give us (1, 2, 3, 4, 5). Now for the cases we have repeating rank we will get the same position of '-' and for the next highest rank, even the concatenated list of ranks wouldn't have the next set of rank making this expression possible to get the number incrementally (look at the three highlighted rows below).

Now if for some reason we think that rank can run above 10,000, but less than 99,999, we might want to change the expression to this:

**Div(Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1), Group, Vendor), '00000'), '-', -Aggr(Count(ID), Group, Vendor)), ****Num(Rank(Count(ID),1,1), '00000')), 6) + 1**

For overall rank, you can modify the expression to something like this:

**Div(Index('-' & Concat(DISTINCT TOTAL Num(Aggr(Rank(TOTAL Count(ID), 1, 1), Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor)), Num(Rank(TOTAL Count(ID),1,1), '0000')), 5) + 1**

I hope this will end up being helpful for someone

Best,

Sunny

## Comments