Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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';
Can you post the complete script?
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;
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
Is it working now?
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.
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