Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am loading data with a where not match statement. Currently i am copying a list from excel manually to specify the data which should not be imported to Qlik.
E.g.
where not wildmatch([OrderLineNo],
'ARQR-PO2024030047',
'ARQR-PO2024030041',
'ARQR-PO2024030040',
'ARQR-PO2024030028',
'ARQR-PO2024030020',
'ARQR-PO2024030008',
'ARQR-PO2024030007')
Is there a way i can load this exception list from an excel sheet rather than have to script the list in the load statement.
There are over 300 values which increase week on week. I do not want to have to add the values manually each time. please let me know if you have any question or need more information.
Thank you
Daniel
Temp:
Load *
INLINE [
ID
A
B
C
];
Exp_list:
LOAD
Concat(ID,chr(39)&','&chr(39)) as Field Resident Temp;
Drop table Temp;
LET vNROWS = NoOfRows('Exp_list');
FOR i=0 to (vNROWS-1)
LET vExpList = chr(39)&Peek( 'Field', i, 'Exp_list')&chr(39);
next;
Using above you can replace your excel with temp table and fields into it. Finally can use vExpList variable in where match condition, something like below:
where not wildmatch([OrderLineNo],$(ExpList))
You could try using the Exist() function in your load statement, it should look something like this:
Tmp_Exclusion:
Load
ExcludedNumbers
From [yourExcelFile.xlsx];
Orders:
Load
OrderLineNo
From [yourDataSource]
Where Not Exists(ExcludedNumbers, OrderLineNo);
Drop Table Tmp_Exclusion;
This should leave you with your data minus the OrderLineNo specified in your Excel.
Let me know, if this worked for you!
Thank you for the suggestions, sorry i have not had a chance to test yet. I will look at the 2 options provided and feedback asap. Thank you, your help is very much appreciated!
Daniel