Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Expert,
Here i want n-2 row for all distinct claim wise SrNo.below are given some sample cases.
OUTPUT--
Claim -- SrNo
C1 -- 5
C2 -- 4
T2:
LOAD * Inline [
Claim, SrNo,date
C1, 1, 01-02-2018
C1,2,01-02-2018
C1,3,04-02-2018
C1,4,05-02-2018
C1,5,04-04-2018
C1,6,03-07-2018
C2,1,01-02-2018
C2,2,11-02-2018
C2,4,19-02-2018
C2,7,15-06-2018
];
I have used below script for claim C1 its given correct value but for C2 onwards values are getting wrong.
T3:
LOAD *,
if(Claim=Peek(Claim),SrNo,Peek('SrNo',-2)) AS Sr1
Resident T2
Order by Claim;
Thanks in advance....!!
Try this
T2:
LOAD * INLINE [
Claim, SrNo, date
C1, 1, 01-02-2018
C1, 2, 01-02-2018
C1, 3, 04-02-2018
C1, 4, 05-02-2018
C1, 5, 04-04-2018
C1, 6, 03-07-2018
C2, 1, 01-02-2018
C2, 2, 11-02-2018
C2, 4, 19-02-2018
C2, 7, 15-06-2018
];
Left Join (T2)
LOAD Claim,
FirstSortedValue(SrNo, -date, 2) as Sr1
Resident T2
Group By Claim;
Try this
T2:
LOAD * INLINE [
Claim, SrNo, date
C1, 1, 01-02-2018
C1, 2, 01-02-2018
C1, 3, 04-02-2018
C1, 4, 05-02-2018
C1, 5, 04-04-2018
C1, 6, 03-07-2018
C2, 1, 01-02-2018
C2, 2, 11-02-2018
C2, 4, 19-02-2018
C2, 7, 15-06-2018
];
Left Join (T2)
LOAD Claim,
FirstSortedValue(SrNo, -date, 2) as Sr1
Resident T2
Group By Claim;