Filter a field by values

[markgraham123]

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

A

B

C

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.

[markgraham123]

@sunny_talwar  Hope you are doing well! Any thoughts?

[dplr-rn]

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

[markgraham123]

In Script please.

[dplr-rn]

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)

[markgraham123]

This is not working as we are filtering by products.

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

[dplr-rn]

share your code

[markgraham123]

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);

[dplr-rn · Accepted solution]

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?

[markgraham123]

You are right. I corrected it in my code.

Thanks a ton for the help! 🙂

It worked!