Repeating times of a value in a table

[farru_scorpio]

Dear Experts,

I have a table where same amounts are being repeated n number of times what I want is to identify those number of repeats and put a marker like below:

// I have

DateCB            CBNumber           Amount
01-May-2021 BR00\21050009 320.00
01-May-2021 BR00\21050005 460.00
01-May-2021 BR00\21050007 460.00
01-May-2021 BR00\21050003 460.00
01-May-2021 BR00\21050033 320.00
01-May-2021 BR00\21050006 310.00
01-May-2021 BR00\21050004 2,225.00
01-May-2021 BR51\21050009 460.00

// I want

Repeats Amount_Marker
1               320-1
1               460-1
2               460-2
3               460-3
2              320-2
1              310-1
1              2225-1
4              460-4

Thank you in advance

Farrukh

[MayilVahanan · Accepted solution]

HI

Try like below

T1:
LOAD * INLINE [
DateCB,CBNumber ,Amount
01-May-2021 ,BR00\21050009, 320.00
01-May-2021 ,BR00\21050005 ,460.00
01-May-2021 ,BR00\21050007 ,460.00
01-May-2021 ,BR00\21050003 ,460.00
01-May-2021 ,BR00\21050033 ,320.00
01-May-2021 ,BR00\21050006 ,310.00
01-May-2021 ,BR00\21050004 ,2225.00
01-May-2021 ,BR51\21050009 ,460.00
];

T2:
Load *, Amount&'_'&Repeat as Amount_Marker;
Load If(Amount <> Peek(Amount), 1, Peek('Repeat')+1) as Repeat, Amount Resident T1 order by Amount;

o/p:

[farru_scorpio]

Dear Mayil,

Perfect, a big thank you.