Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Filter a field by values

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:

OrderProductsResult
1ALoad
1B
1C
2ALoad
2B
3BExclude - As we have Product -'D'
3C
3D
4AExclude As we have Product - 'D'
4C
4D
5AExclude As we have Products -  'E' and 'F'
5E
5F

 

Can someone please help? Any help is highly appreciated.

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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?

 

View solution in original post

9 Replies
Highlighted
Specialist
Specialist

@sunny_talwar  Hope you are doing well! Any thoughts?

Highlighted
Partner
Partner

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

Highlighted
Specialist
Specialist

In Script please.

Highlighted
Partner
Partner

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)
Highlighted
Specialist
Specialist

This is not working as we are filtering by products.

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

Highlighted
Partner
Partner

share your code

Highlighted
Specialist
Specialist

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

Highlighted
Partner
Partner

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?

 

View solution in original post

Highlighted
Specialist
Specialist

You are right. I corrected it in my code.

Thanks a ton for the help! 🙂

It worked!