Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Filtering Data

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;




1 Solution

Accepted Solutions
MVP
MVP

Re: Filtering Data

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;

8 Replies
MVP
MVP

Re: Filtering Data

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;

Not applicable

Re: Filtering Data

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';

MVP
MVP

Re: Filtering Data

Can you post the complete script?

Not applicable

Re: Filtering Data

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);

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';

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;

MVP
MVP

Re: Filtering Data

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

MVP
MVP

Re: Filtering Data

Is it working now?

Not applicable

Re: Filtering Data

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. 

MVP
MVP

Re: Filtering Data

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