Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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');
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');
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$);
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');
Thank you so much! That worked!