Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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;

View solution in original post

8 Replies
sunny_talwar

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
Author

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

sunny_talwar

Can you post the complete script?

Not applicable
Author

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;

sunny_talwar

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

sunny_talwar

Is it working now?

Not applicable
Author

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. 

sunny_talwar

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