Filter line that doesn't have Pair line.

[RC_121985]

Hi All,

I have data set where each line has par value with uniq code and the step. I mean, there different steps for each Item, and I would like to filter only the line that doesn't have Pair line. 

Sample:

ITEM	CHECK
ABC	Pre
ABC	Cleared
DEF	Pre
DEF	Cleared
XYZ	Pre

In the above Data ITEM ABC and DEF has 2 steps that are cleared XYZ that is not cleared yet. I would like to filter out all the Cleared items. That mean I need only the one Item from above is XYZ that is pending to be cleared.

Currently I am achieving that by below script;

[CLEAR]:
LOAD Distinct

ITEM as CLRITEM

FROM  Sample where CHECK='Cleared';

[OPEN]:

Load Distinct

ITEM

FROM Sample where CHECK='Pre' and Not Exists(CLRITEM, ITEM) ;

Drop Table [CLEAR];

Is there any way without loading twice or any short cut to achieve this?

Thanks in Advance.

[sidhiq91]

@RC_121985  Assuming there is always 2 steps and Cleared is always after Pre, you can use the below Idea.

NoConcatenate
Temp:
Load * Inline [
ITEM, CHECK
ABC, Pre
ABC, Cleared
DEF, Pre
DEF, Cleared
XYZ, Pre
](delimiter is ',');

NoConcatenate
Temp1:
Load ITEM, [Count of Item]
where [Count of Item]='1';

Load Count(ITEM) as [Count of Item],
ITEM
//CHECK
REsident Temp
Group by ITEM;

Drop table Temp;

exit script;

Let me know if it helps you.

[rwunderlich · Accepted solution]

Here's a similar approach counting CHECK = 'Cleared' rather the the total line count:

Data:
Load * Inline [
ITEM, CHECK
ABC, Pre
ABC, Cleared
DEF, Pre
DEF, Cleared
XYZ, Pre
];

Inner Join(Data)
LOAD ITEM
Where Count = 0
;
LOAD ITEM,
Sum(CHECK = 'Cleared') as Count
Resident Data
Group By ITEM
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[sidhiq91]

@rwunderlich  Thanks much for another approach. Just curious to know what would this particular statement do Sum(CHECK = 'Cleared') as Count?

[rwunderlich]

That clause would generate a field named "Count" that contains a count of the times when the CHECK field = 'Cleared'. 

CHECK = 'Cleared' // -1 if true, 0 if false

Sum(CHECK = 'Cleared') // Will sum up all the -1 and 0 results

The preceding load would then select only rows (ITEMs) where Count = 0.

-Rob

[sidhiq91]

@rwunderlich Thank you so much for the explanation Rob.

[RC_121985]

Not always 2 Steps, I have Multiple check for same Item.

[RC_121985]

Thank you Rob, It worked very well. 👍