Generate index based on grouped dimensions

[devarshigoswami]

I have a table that looks like this (this is only a part of the data)

Store	ID	Fruit	Date	Month
A	1	apple	01/04/22	Apr-22
A	1	banana	03/04/22	Apr-22
A	1	cherry	05/04/22	Apr-22
A	2	apple	07/04/22	Apr-22
A	2	banana	09/04/22	Apr-22
A	2	cherry	11/04/22	Apr-22
A	14	banana	13/04/22	Apr-22
A	13	banana	15/04/22	Apr-22
A	13	blackberry	17/04/22	Apr-22
A	12	apple	19/04/22	Apr-22
B	1	apple	12/05/22	May-22

I needed to generate a number/index for every month for every fruit  like so...

Store	ID	Fruit	Date	Month	suffix
A	1	apple	01/04/22	Apr-22	1
A	1	banana	03/04/22	Apr-22	1
A	1	cherry	05/04/22	Apr-22	1
A	2	apple	07/04/22	Apr-22	2
A	2	banana	09/04/22	Apr-22	2
A	2	cherry	11/04/22	Apr-22	2
A	14	banana	13/04/22	Apr-22	3
A	13	banana	15/04/22	Apr-22	4
A	13	blackberry	17/04/22	Apr-22	1
A	12	apple	19/04/22	Apr-22	3
B	1	apple	12/05/22	May-22	1

Here, the field suffix has been created on the basis of the fields Fruit and Month

For the month of Apr-2022 ID=1 was sold an apple, a banana, and a cherry.

Since that was the first apple, banana and cherry sold it gets an index number of 1. 

Similarly, the subsequent apple sold in that month will get an index of 2.

If the month is May, the first apple sold will get the index of 1 again.

can this logic be achieved in the load script , if so what do I use? 

AutoNumber()? 

[Digvijay_Singh · Accepted solution]

I think quite easier to do that in front-end like this - 

In the script you may do it by creating new field Rowno() and Order by your table with Month, Fruit and ID. 

But First I would like to confirm if the above logic works with your real data