LOAD MAX and 2nd MAX and Data In Between

racer25 — Tue, 30 May 2017 13:50:39 GMT

Hi All,

I am looking to load some information from a 20 year old data table; I am looking to get the last "R" transaction and also every PLY_VERNO back to the previous R or N record. Highlighted in the table below in red are the rows I wish to load. As you can in 0023879 we capture 6 rows whereas 0023878 only 2.

I have thought about this and have to admit to not really having much on how to deliver this. I'd appreciate some help on this please.

Thanks,

Rob

PLY_COVERNUMBER	PLY_VERNO	PLY_TRANSACTIONTYPE
0023877	005	E
0023877	006	R
0023877	007	R
0023877	008	N
0023877	009	E
0023877	010	R
0023877	010	L
0023878	000	N
0023878	001	R
0023878	002	R
0023878	003	R
0023878	004	R
0023878	005	R
0023878	006	R
0023878	007	L
0023879	014	R
0023879	015	R
0023879	016	R
0023879	017	E
0023879	018	R
0023879	019	R
0023879	020	R
0023879	021	E
0023879	022	E
0023879	023	E
0023879	024	E
0023879	025	R

Re: LOAD MAX and 2nd MAX and Data In Between

settu_periasamy — Tue, 30 May 2017 19:47:33 GMT

Maybe there is an easy method. I tried this..

T1:

LOAD * INLINE [

PLY_COVERNUMBER, PLY_VERNO, PLY_TRANSACTIONTYPE

0023877, 5, E

0023877, 6, R

0023877, 7, R

0023877, 8, N

0023877, 9, E

0023877, 10, R

0023877, 10, L

0023878, 0, N

0023878, 1, R

0023878, 2, R

0023878, 3, R

0023878, 4, R

0023878, 5, R

0023878, 6, R

0023878, 7, L

0023879, 14, R

0023879, 15, R

0023879, 16, R

0023879, 17, E

0023879, 18, R

0023879, 19, R

0023879, 20, R

0023879, 21, E

0023879, 22, E

0023879, 23, E

0023879, 24, E

0023879, 25, R

];

NoConcatenate

T2:

LOAD PLY_COVERNUMBER

,Max(PLY_VERNO) as PLY_VERNO

,PLY_TRANSACTIONTYPE,'Max' as Temp

Resident T1 Where PLY_TRANSACTIONTYPE='R'

Group by PLY_COVERNUMBER,PLY_TRANSACTIONTYPE

Order By PLY_COVERNUMBER,PLY_VERNO;

Concatenate(T2)

LOAD PLY_COVERNUMBER

,Max(PLY_VERNO,2) as PLY_VERNO,

PLY_TRANSACTIONTYPE,'Max2' as Temp

Resident T1 Where PLY_TRANSACTIONTYPE='R'

Group by PLY_COVERNUMBER,PLY_TRANSACTIONTYPE

Order By PLY_COVERNUMBER,PLY_VERNO;

Left Join(T1)

LOAD * Resident T2;

DROP Table T2;

NoConcatenate

T3:

LOAD PLY_COVERNUMBER

,PLY_VERNO

,PLY_TRANSACTIONTYPE

,if(Temp='Max' or Temp='Max2','Yes',

if(Peek(Temp1)='Yes' and PLY_COVERNUMBER=Previous(PLY_COVERNUMBER) and Previous(Temp)<>'Max','Yes','No') ) as Temp1

Resident T1;

DROP Table T1;

NoConcatenate

Final:

LOAD PLY_COVERNUMBER,

PLY_VERNO,

PLY_TRANSACTIONTYPE

Resident T3 Where Temp1='Yes';

DROP Table T3;

topic LOAD MAX and 2nd MAX and Data In Between in QlikView

LOAD MAX and 2nd MAX and Data In Between

Re: LOAD MAX and 2nd MAX and Data In Between