Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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:
Dear Mayil,
Perfect, a big thank you.