topic Re: Filter a field by values in QlikView

Filter a field by values

markgraham123 — Sat, 16 Nov 2024 00:11:10 GMT

Hi all,

I'm working on a task to pull only the orders which have Products - A,B,C in any combinations.

As long as an order has only those Products, i want to pull the order, else i want to exclude it.

Here is Sample data:

Products

Output:

Order	Products	Result
1	A	Load
1	B
1	C
2	A	Load
2	B
3	B	Exclude - As we have Product -'D'
3	C
3	D
4	A	Exclude As we have Product - 'D'
4	C
4	D
5	A	Exclude As we have Products - 'E' and 'F'
5	E
5	F

Can someone please help? Any help is highly appreciated.

Re: Filter a field by values

markgraham123 — Thu, 06 Aug 2020 17:02:32 GMT

@sunny_talwar Hope you are doing well! Any thoughts?

Re: Filter a field by values

dplr-rn — Thu, 06 Aug 2020 17:05:10 GMT

do you want to do this in script? or on chart?

Re: Filter a field by values

markgraham123 — Thu, 06 Aug 2020 17:07:09 GMT

In Script please.

Re: Filter a field by values

dplr-rn — Thu, 06 Aug 2020 17:11:04 GMT

follow below approach

//Step 1 load orders with other products load distinct Order as ExcludeOrder from yoursource where not match(Products,'A','B','C') //Step 2 load data not in above load * from yoursource where not exists(ExcludeOrder,Order)

Re: Filter a field by values

markgraham123 — Thu, 06 Aug 2020 17:28:48 GMT

This is not working as we are filtering by products.

In this case, i still get the orders 3,4,5.

Re: Filter a field by values

dplr-rn — Thu, 06 Aug 2020 17:31:55 GMT

share your code

Re: Filter a field by values

markgraham123 — Thu, 06 Aug 2020 17:38:56 GMT

SKU_Required: //This is where i have all my products that are to be loaded
LOAD item_number as SKU
FROM
[C:\Users\Desktop\SKUS.qvd]
(qvd);

Orders:
load distinct Order as Exclude_Order //This section is bringing in all orders
FROM
[C:\Users\Desktop\Orders.qvd]
(qvd)
where not exists(SKU, item_number);

Re: Filter a field by values

dplr-rn — Thu, 06 Aug 2020 17:48:05 GMT

in my dummy code first load i am loading all the order with products other than a,b,c i.e. orderids which should be excluded

in second load you should exlcude these.

you code doesnt seem to be doing that

question for you whats the SKU.qvd do?

Re: Filter a field by values

markgraham123 — Thu, 06 Aug 2020 18:06:14 GMT

You are right. I corrected it in my code.

Thanks a ton for the help! 🙂

It worked!