topic Re: Filter line that doesn't have Pair line. in App Development

Filter line that doesn't have Pair line.

RC_121985 — Fri, 15 Jul 2022 13:07:48 GMT

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.

Re: Filter line that doesn't have Pair line.

sidhiq91 — Fri, 15 Jul 2022 14:32:12 GMT

@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.

Re: Filter line that doesn't have Pair line.

rwunderlich — Fri, 15 Jul 2022 16:02:07 GMT

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

Re: Filter line that doesn't have Pair line.

sidhiq91 — Sat, 16 Jul 2022 02:26:42 GMT

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

Re: Filter line that doesn't have Pair line.

rwunderlich — Mon, 18 Jul 2022 14:16:38 GMT

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

Re: Filter line that doesn't have Pair line.

sidhiq91 — Tue, 19 Jul 2022 02:21:41 GMT

@rwunderlich Thank you so much for the explanation Rob.

Re: Filter line that doesn't have Pair line.

RC_121985 — Thu, 21 Jul 2022 07:34:33 GMT

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

Re: Filter line that doesn't have Pair line.

RC_121985 — Thu, 21 Jul 2022 09:05:49 GMT

Thank you Rob, It worked very well. 👍