Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

beginnertom
New Contributor III

Qlikview selection pick based on conditions

Hi everyone,

i have an example data as below,i would like to load the data 

if order by batch no and ODATE, the 1st-row type =C, 2ndrow type =D only load first row data, otherwise, load second-row data order by ODATE

below highlighted in red is the expected output.

is it possible to do this in Qlikview. Thanks!

batch notypeindexODATE
AC1102019.07.11
AD1112019.07.12
AC1122019.07.13
AD1132019.07.14
AD1142019.07.15
AC1152019.07.16
BC1162019.07.17
BC1172019.07.18
BD1182019.07.19
BC1192019.07.20
BC1202019.07.21
BD1212019.07.22
1 Reply

Re: Qlikview selection pick based on conditions

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;