Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
deepak_gupta
Partner - Contributor II
Partner - Contributor II

N-2 row for each records in same dimension

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

1 Reply
sunny_talwar

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;