N-2 row for each records in same dimension

deepak_gupta — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: N-2 row for each records in same dimension

sunny_talwar — Fri, 13 Jul 2018 11:39:58 GMT

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;

topic Re: N-2 row for each records in same dimension in QlikView

N-2 row for each records in same dimension

Re: N-2 row for each records in same dimension