How to exclude data

[rmalkus]

I have a Product column and I am trying to figure out how to exclude a few of the product types from the data.

[vinieme12]

there are multiple ways to reduce data during load

1) using wildmatch

Load *

Where Wildmatch(Product,'apple','samsung')=0;  // here  =0 will exclude apple and samsung during load

1) using Exists

ExcludeProducts:

Load * Inline [

exProduct

Apple

Samsung

];

Fact:

Load *

Where Not Exists(exProduct,Product);  //  this will filter data based on values availabe in exProduct field

You can also use inner join or inner keep

Refer below for more

https://community.qlik.com/t5/QlikView-App-Dev/Understanding-Join-Keep-and-Concatenate/td-p/328379

[HugoRomeira_PT]

Hello,

If you want to always excluded the data associated with some product type you should apply a Where clause in the Load script:

LOAD *

Resident tablename

WHERE ( [Product Type]<>'X' AND [Product Type]<>'Y' )

In case you want to exclude several values you can you the function NOT MATCH:

LOAD *

from tablename

where not match([Product Type], 'X','Y','Z')

;

[rmalkus]

I am getting an error when using NOT MATCH script.  Please see my below my code.  

LOAD

OrderID,
ProductID,
Product,
from Order
where not match([Product Type], 'Aino Shoes','Lace Shoes','Runner Shoes')
CustomerID,
EmployeeID,
Year(OrderDate)as Year,
Month (OrderDate) as Month,
Week (OrderDate) as Week,
Sales,
Cost,
Quantity

[HugoRomeira_PT]

The FROM/Resident Order and where not match([Product Type], 'Aino Shoes','Lace Shoes','Runner Shoes') seems to be in the wrong place.

Try the following:

LOAD
OrderID,
ProductID,
Product,
CustomerID,
EmployeeID,
Year(OrderDate)as Year,
Month (OrderDate) as Month,
Week (OrderDate) as Week,
Sales,
Cost,
Quantity

Resident Order 
where not match([Product Type], 'Aino Shoes','Lace Shoes','Runner Shoes');

Note: Syntax for load is: 
LOAD field1

         , field2
        ,....
FROM (or RESIDENT) tablename
WHERE (conditions)

[rmalkus]

[lib://Dropbox - robinmalkus@gmail.com/Sales Test Data.xls]  //Is now showing up in red.  

Load

OrderID,
ProductID,
Product,
CustomerID,
EmployeeID,
Year(OrderDate)as Year,
Month (OrderDate) as Month,
Week (OrderDate) as Week,
Sales,
Cost,
Quantity

Resident Order
where not match([Product Type], 'Aino Shoes','Lace Shoes','Runner Shoes');

FROM [lib://Dropbox - robinmalkus@gmail.com/Sales Test Data.xls]
(biff, embedded labels, table is Orders$);

[HugoRomeira_PT]

What about now?

Orders:

Load
OrderID,
ProductID,
Product,
CustomerID,
EmployeeID,
Year(OrderDate)as Year,
Month (OrderDate) as Month,
Week (OrderDate) as Week,
Sales,
Cost,
Quantity

FROM [lib://Dropbox - robinmalkus@gmail.com/Sales Test Data.xls]
(biff, embedded labels, table is Orders$)

where not match([Product Type], 'Aino Shoes','Lace Shoes','Runner Shoes');

[rmalkus · Accepted solution]

I changed the Product Type to Product and it worked.   

Thanks for your help.  

[HugoRomeira_PT]

Nice to know that we could help you 😀
Have a nice day!