topic Re: Filtering Data in App Development

Filtering Data

Sat, 10 Oct 2015 19:22:12 GMT

I have a very large data set and I'm trying to filter out the data I don't need during the load process. I've been able to filter Material using the Where Not Exists() below, but I still need to filter further.

In the MainTable the Material column contains a value called Service Contract and an Order column that contains a contact number value (ex. 400234234). I've been able to pull a list of all contract values (Order column) that I need to keep (as outlined in IncludedFile below). Now I'm trying filter out all rows that contain a contract values that are not included in the IncludedFile where the Material is equal to Service Contract.

I'm thinking that I need to do some sort of combine of Material and Order in the IncludedContracts table to create a unique key and then use a Where Exists() but I can't seem to get that to work. (items commented out won't work but show thought process).

I'm pretty new to Qlik so any thoughts or suggestions would be greatly appreciated.

ExcludeMaterial:

LOAD

Material as ExcludedMaterial

FROM ExcludedFile;

NoConcatenate

IncludedContracts:

LOAD

Material,

"Order",

//Material&"Order" as ContractKey

FROM IncludedFile;

NoConcatenate

MainTable:

LOAD Material,

"Orders",

AllOtherColumns

FROM MainFile

Where Not Exists (ExcludedMaterial,Material);

//Where Exists (ContractKey,Material&"Orders");

DROP Table ExcludeMaterial;

DROP Table IncludedContracts;

Re: Filtering Data

sunny_talwar — Sat, 10 Oct 2015 23:51:27 GMT

If I understand your requirement, may be this:

ExcludeMaterial:

LOAD

Material as ExcludedMaterial

FROM ExcludedFile;

TempTable:

LOAD Material,

Orders,

AllOtherColumns

FROM MainFile

Where Not Exists (ExcludedMaterial,Material);

IncludedContracts:

LOAD Orders as IncludeOrders

FROM IncludedFile;

NoConcatenate

MainTable:

LOAD Material,

Orders,

AllOtherColumns

Resident TempTable

Where Exists (IncludeOrders, Orders) and Material = 'Service Contract';

Concatenate (MainTable)

LOAD Material,

Orders,

AllOtherColumns

Resident TempTable

Where not Material = 'Service Contract';

DROP Tables ExcludeMaterial, IncludedContracts, TempTable;

Re: Filtering Data

Sun, 11 Oct 2015 02:07:46 GMT

I'm trying to get the data to load but keep getting the following error: Field not found - <Line Itm>. The error seems to be occurring in the section below. Thoughts?

NoConcatenate
MainTable:
LOAD
    Material,
    Customer,
    "Customer Name" as CustomerName,
    "Sub Category 1" as SubCategory1,
    "Sub Category 2" as SubCategory2,
    "Order Type" as OrderType,
    "Invoice Type" as InvoiceType,
    "Sales Region" as SalesRegion,
    "Order",
    Reference
Resident TempTable
Where Exists (Includeorders,"Order") and Material='SERVICE CONTRACT';

Re: Filtering Data

sunny_talwar — Sun, 11 Oct 2015 14:41:56 GMT

Can you post the complete script?

Re: Filtering Data

Sun, 11 Oct 2015 14:56:53 GMT

Here it is...

ExcludeMaterial:
LOAD
Material as Material_Excl
FROM [lib://ProductLine/Product Line (Software and Hardware)\ServiceException.xlsx]
(ooxml, embedded labels, table is Sheet1);

TempTable:
LOAD
    Material,
    Customer,
    "Customer Name" as CustomerName,
    "Sub Category 1" as SubCategory1,
    "Sub Category 2" as SubCategory2,
    "Order Type" as OrderType,
    "Invoice Type" as InvoiceType,
    "Sales Region" as SalesRegion,
    "Order",
    Reference
FROM [lib://ProductLine/testComputerService 2011 through Oct 8 2015.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE NOT EXISTS (Material_Excl,Material)
;

NoConcatenate
IncludedContracts:
LOAD
"Order" as Includeorders
FROM [lib://ProductLine/Product Line (Software and Hardware)\SoftwareContractFilter.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(MainTable)
LOAD
    Material,
    Customer,
    "Customer Name" as CustomerName,
    "Sub Category 1" as SubCategory1,
    "Sub Category 2" as SubCategory2,
    "Order Type" as OrderType,
    "Invoice Type" as InvoiceType,
    "Sales Region" as SalesRegion,
    "Order",
    Reference
Resident TempTable
Where not Material='SERVICE CONTRACT';

DROP Tables ExcludeMaterial,IncludedContracts,TempTable;

Re: Filtering Data

sunny_talwar — Sun, 11 Oct 2015 15:21:41 GMT

Try this:

ExcludeMaterial:
LOAD
Material as Material_Excl
FROM [lib://ProductLine/Product Line (Software and Hardware)\ServiceException.xlsx]
(ooxml, embedded labels, table is Sheet1);

TempTable:
LOAD
    Material,
    Customer,
    "Customer Name" as CustomerName,
    "Sub Category 1" as SubCategory1,
    "Sub Category 2" as SubCategory2,
    "Order Type" as OrderType,
    "Invoice Type" as InvoiceType,
    "Sales Region" as SalesRegion,
    "Order",
    Reference
FROM [lib://ProductLine/testComputerService 2011 through Oct 8 2015.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE NOT EXISTS (Material_Excl,Material);

NoConcatenate
IncludedContracts:
LOAD
"Order" as Includeorders
FROM [lib://ProductLine/Product Line (Software and Hardware)\SoftwareContractFilter.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
MainTable:
LOAD
    Material,
    Customer,
    CustomerName,
    SubCategory1,
    SubCategory2,
    OrderType,
    InvoiceType,
    SalesRegion,
    "Order",
    Reference
Resident TempTable
Where Exists (Includeorders,"Order") and Material='SERVICE CONTRACT';

Concatenate(MainTable)
LOAD
    Material,
    Customer,
    CustomerName,
    SubCategory1,
    SubCategory2,
    OrderType,
    InvoiceType,
    SalesRegion,
    "Order",
    Reference
Resident TempTable
Where not Material='SERVICE CONTRACT';

DROP Tables ExcludeMaterial,IncludedContracts,TempTable;

Since you are using a resident load from TempTable, you need to use their new names instead of the old ones.

Best,

Sunny

Re: Filtering Data

sunny_talwar — Sun, 11 Oct 2015 15:48:58 GMT

Is it working now?

Re: Filtering Data

Sun, 11 Oct 2015 15:55:57 GMT

The script ran successfully - now validating the solution and will mark your initial reply as correct answer once complete. I greatly appreciate you helping me with this problem. I've learned a lot during this process.

Re: Filtering Data

sunny_talwar — Sun, 11 Oct 2015 18:42:42 GMT

That is the idea... That we all get to learn from each other. I am glad that you got to learn something from me today.

Best,

Sunny