A comparison with Match() will work fine.
Where not Match( relationNumber, $(FinancialRelations));
But I would probably use a Where not Exists() instead - since you probably have the numbers to exclude in a table already. Hence
SQL SELECT RelationsToExclude FROM ... ;
Load * Where not Exists(RelationsToExclude, Relation);
SQL SELECT * FROM ... ;
Drop Table RelationsToExclude;
1: I didn't know that where not exists() accepts more than 1 parameter (and therefore I thought that my relations table had to have the "relationsToExclude" column)... So this is something I learn (and will test asap). Thanks!
2: I don't understand why the comparison with match will work fine...
- $(FinancialRelations) is one large string with commaseparated relationnumbers...
- when this string contains the value 100 ( so relation 100 must be excluded)
- and my relation table contains relation number 1 and 10... then I assume that Where not Match( relationNumber, $(FinancialRelations)); also excludes 1 and 10...
Or am I mistaking?
There is no other answer possible than You're absolutely right!
Thanks for your answer and support!
Apparently Qlikview doesn't treat the variable FinancialRelations as a string (...).
Even when I enclose the relations with singlequotes (like below), it still works like I want (it excludes the 100 and 10.000).
Set FinancialRelations = '100, 10000';
Good to know!
Whether you have single quotes or not, doesn't matter. The variable FinancialRelations will in both cases have a value equal to a string: '100, 10000'.
The magic is in the dollar-expansion. Right before execution of a statement, QlikView will exchange all $(variable) -constructions with the variable value. I.e.
Load * Where not Match( relationNumber, $(FinancialRelations));
will be exchanged with
Load * Where not Match( relationNumber, 100, 10000);
And when this statement is executed, the field relationNumber will be compared with 100 and 10000.