Hi, I'm not sure because higlighted rows and explanation says different things, as an idea:
OriginalData:
LOAD *, Date(Date#(OrigDATE, 'YYYY.MM.DD')) as ODATE INLINE [
batch no, type, index, OrigDATE
A, C, 110, 2019.07.11
A, D, 111, 2019.07.12
A, C, 112, 2019.07.13
A, D, 113, 2019.07.14
A, D, 114, 2019.07.15
A, C, 115, 2019.07.16
B, C, 116, 2019.07.17
B, C, 117, 2019.07.18
B, D, 118, 2019.07.19
B, C, 119, 2019.07.20
B, C, 120, 2019.07.21
B, D, 121, 2019.07.22
];
// Keep only 2 first rows for each batch no
Keep2Rows:
NoConcatenate LOAD [batch no], Min(ODATE) as ODATE Resident OriginalData Group By [batch no];
Concatenate LOAD [batch no], Min(ODATE, 2) as ODATE Resident OriginalData Group By [batch no];
Inner Join LOAD * Resident OriginalData;
// Sorted load to check if first row is C and second row is D
FirstPass:
LOAD *,
If(Peek([batch no])=[batch no] and Peek(type)='C' and type='D', 1) as FilterSecond
Resident Keep2Rows order by [batch no], ODATE;
// Other cases (first row<>C or second row<>D)
SecondPass:
NoConcatenate LOAD *,
If(Peek([batch no])=[batch no] and Peek(type)<>'D' or type<>'C', 1) as FilterFirst
Resident FirstPass order by [batch no], ODATE desc;
// Keep only flagged records
EndTable:
NoConcatenate LOAD [batch no], type, index, ODATE
Resident SecondPass
Where (FilterFirst or FilterSecond);
// Delete temporary tables
DROP Tables OriginalData, Keep2Rows, FirstPass, SecondPass;
