N-2 row for each records in same dimension

[deepak_gupta]

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....!!

[sunny_talwar · Accepted solution]

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;