Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
vprofessional
Contributor II
Contributor II

Data exclusion

Hey guys, I want to exclude some products that a company doesn't want for their analytics. I have a CSV file with 'Product' as the name of the column and the name of the products under it. I need the code after the LOAD command to only get the data from the table that doesn't include certain products. What transformation can I use to exclude values in the Data load editor? 

Labels (4)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

LOAD

OrderID,

ProductID,

Product,

CustomerID,

EmployeeID,

OrderDate,

Year(OrderDate) as Year,

Month(OrderDate) as Month,

Week(OrderDate) as Week,

Sales,

If(Sales >= 5000, 1) as LargeSaleFlag,

Cost,

Sales - Cost as Margin,

Quantity

FROM [lib://DataFiles/Sales.xls](biff, embedded labels, table is Orders$)

WHERE not Match(Product, 'ProductA', 'ProductB', 'ProductX');

 

View solution in original post

4 Replies
Vegar
MVP
MVP

You need a WHERE cause in your LOAD statement. Try something like this.

Load * From Product.csv (txt)

WHERE not Match(Product, 'ProductA','ProductB','ProductX');

vprofessional
Contributor II
Contributor II
Author

Hey, thanks for the reply. This is my code so far. I am getting errors if I replace the last line. According to you, where should I put this "where" exclusion code?

 

Orders:
LOAD
OrderID,
ProductID,
Product,
CustomerID,
EmployeeID,
OrderDate,
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Week(OrderDate) as Week,
Sales,
If(Sales >= 5000, 1) as LargeSaleFlag,
Cost,
Sales - Cost as Margin,
Quantity
FROM [lib://DataFiles/Sales.xls](biff, embedded labels, table is Orders$);

Vegar
MVP
MVP

LOAD

OrderID,

ProductID,

Product,

CustomerID,

EmployeeID,

OrderDate,

Year(OrderDate) as Year,

Month(OrderDate) as Month,

Week(OrderDate) as Week,

Sales,

If(Sales >= 5000, 1) as LargeSaleFlag,

Cost,

Sales - Cost as Margin,

Quantity

FROM [lib://DataFiles/Sales.xls](biff, embedded labels, table is Orders$)

WHERE not Match(Product, 'ProductA', 'ProductB', 'ProductX');

 

vprofessional
Contributor II
Contributor II
Author

Thank you so much! That worked!