Creating a new coloum with number of occurrences but in chronological order

[sanket94]

Greetings all,

  I was trying to create a column that counts the occurrences/ repetition of my keys, in chronological order . For example if my key is found for first time and then when it is repeated for second time it should put 2 when it repeats the third time it should show 3.

By based on key. 

For the below example, I have left table I want to create the field Counts in the data load editor.

 

If I could order it by Quantity as well it would be great.

[joseph_morales · Accepted solution]

Hi @sanket94 ,

You can try using autonumber in the script like this:

TABLE_TEMP:
Load * Inline [
KEY, NAME, QTY
A1, A, 100
A1, B, 500
A2, C, 600
A1, D, 800
A4, E, 500
A4, F, 100
A1, C, 300
];

//Order table
TABLE:
Load *,AutoNumber(KEY&'-'&NAME&'-'&QTY,KEY)
Resident TABLE_TEMP
;

drop table TABLE_TEMP;

[sanket94]

@joseph_morales : Thank you that helped me. if I have a date column could I order it by date? 

I appreciate your help 🙂

[joseph_morales · Accepted solution]

Hi @sanket94 

You must use group by for the DATE field:

//Order table
TABLE:
Load *,AutoNumber(KEY&'-'&DATE&'-'&NAME&'-'&QTY,KEY)
Resident TABLE_TEMP
GROUP BY DATE
;

drop table TABLE_TEMP;