How to reference data in saved location in script for a not match load.

[Daniel77]

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

[HirisH_V7]

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

[lennart_mo]

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!

[Daniel77]

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