Auto Generate Reference No

[KKumar92]

Hi All,

I am working on a sample data-set as follow;

Ref_ID	Item	Date 'D/M/YYYY'	Qty
1	X	1/2/2021	0
2	X	2/2/2021	10
1	X	3/2/2021	0
2	X	5/2/2021	15

For the above data-set I want to add a new column named as "Identifier_No" and please refer the table below for the desired output.

Identifier_No	Ref_ID	Item	Date 'D/M/YYYY'	Qty
1	1	X	1/2/2021	0
1	2	X	2/2/2021	10
2	1	X	3/2/2021	0
2	2	X	5/2/2021	15

Please advise on the above and how the desired output could be achieved.

Thanks.

[Chanty4u]

what is the logic to built that column?  

on which basis the values will come? can you explain more?

[KKumar92]

As you could see, the Ref_ID column repeats 1 and 2, here 1 means Qty = 0 and 2 Means Qty > 0.

Likewise, for a given period there could "n" number of such occurrences. Therefore, considering the above example first instance where it have qty 0 and 10, I want group it as one and generate a Identifier No 1, and for the second instance I want to group qty  0 and 15 and generate Identifier 2, and the number sequence will be continued as far as there are more groups.

Hope this would clarify your doubts on the above.

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD *, If(Qty=0,RangeSum(1,Peek(Identifier_No)), If(Qty>0, Peek(Identifier_No))) As Identifier_No;
LOAD * INLINE [
    Ref_ID, Item, "Date 'D/M/YYYY'", Qty
    1, X, 1/2/2021, 0
    2, X, 2/2/2021, 10
    1, X, 3/2/2021, 0
    2, X, 5/2/2021, 15
    1, X, 8/2/2021, 0
    2, X, 9/2/2021, 25
];

[Saravanan_Desingh]

Output:

[KKumar92]

Hi Saran,

Thanks for the solution suggested. I am getting the desired output from the script.