Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mfigueiredo
Contributor III
Contributor III

Filtering two fields in two different tables in Script

Dear Qlik Sense Experts,

I have the following LOAD script, simplified due to confidential data:

Ord_item:
LOAD
Orders,
ProductCd,
Value,
Quantity,
Customer,
Market,
Date
FROM qvd ABC;

Product:
LEFT Keep (Ord_item)
LOAD
ProductCd,
ProductDetail,
ProductType,
Brand,
SubBrand,
Format
FROM qvd XYZ;

What I need is to filter my data with the following conditions:
1) Load only the Products: Apples, Oranges and Pinneaples from the field ProductDetail
2) Additionally, load the Product Cherries (field is ProductDetail same as above), but from only France (field is Market).

I am struggling with Resident and Join and dropping tables and in spite of searching and trying more than 50 solutions my script still has errors...

Can you please help me with the syntax for filtering this data?

Thank you
Mariana

 

Labels (2)
1 Solution

Accepted Solutions
Digvijay_Singh

May be something like this - 

Ord_item:
LOAD
Orders,
ProductCd,
Value,
Quantity,
Customer,
Market,
Date
FROM qvd ABC;

Product:
LEFT Keep (Ord_item)
LOAD
ProductCd,
ProductDetail,
ProductType,
Brand,
SubBrand,
Format
FROM qvd XYZ;


noconcatenate

Ord_item_final:
Load *
resident Ord_item
Where Match(ProductDetail,'Apples','Oranges','Pinneaples') OR (ProductDetail='Cherries' and Market='France');

Drop table Ord_item;

View solution in original post

2 Replies
Digvijay_Singh

May be something like this - 

Ord_item:
LOAD
Orders,
ProductCd,
Value,
Quantity,
Customer,
Market,
Date
FROM qvd ABC;

Product:
LEFT Keep (Ord_item)
LOAD
ProductCd,
ProductDetail,
ProductType,
Brand,
SubBrand,
Format
FROM qvd XYZ;


noconcatenate

Ord_item_final:
Load *
resident Ord_item
Where Match(ProductDetail,'Apples','Oranges','Pinneaples') OR (ProductDetail='Cherries' and Market='France');

Drop table Ord_item;

mfigueiredo
Contributor III
Contributor III
Author

Thanks! I just tested it and was missing the syntax for the resident and was not using OR.

So simple! I feel a bit silly.

 

Thank you again!

Mariana