LOAD MAX and 2nd MAX and Data In Between

[racer25]

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

[settu_periasamy · Accepted solution]

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;