Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
A scripting question:
In our loadscript we have some code like
SET FinancialRelations = '123,14413,2417,2342340,24444566,5555,8888,9999';
The idea is that the (comma separated) relationnumbers listed in that variable are excluded in some loadstatements (like when loading the relations table).
The current implementation is
where match(relationNumber,$(FinancialRelations ))=0
>>I think this can be risky, for eg when relation 100 must be excluded and relation 1 and 10 exists... I think that then relation 1 and 10 are excluded (and should not be).
What is the easiest way to exclude those relations?
in SQL I would just write
select * FROM relations where relationNumber not in (select number from RelationsToExclude)
I know I can load the RelationsToExclude in an inline table with this script
RelationsToExclude:
load Num(SubField('$(BoekhoudkundigeRelaties)',',')) as RelationsToExclude
AutoGenerate(1);
Andd probably the easiest way is to use where not exists.... But I wonder if there is no easier way... Since when using not exists... I need the field relationsToExclude also in my relationsTable which I do not need (and I need an additional resident load...)
Or am I just making things to complicated and is there an easier way?
It will only exclude the listed values and no other. Try the following and you'll see:
Set FinancialRelations = 100, 10000 ;
Load * Where not Match( relationNumber, $(FinancialRelations));
Load
'1'&repeat('0',recno()-1) as relationNumber
autogenerate 10;
HIC
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
RelationsToExclude:
SQL SELECT RelationsToExclude FROM ... ;
RelationsToInclude:
Load * Where not Exists(RelationsToExclude, Relation);
SQL SELECT * FROM ... ;
Drop Table RelationsToExclude;
HIC
2 things:
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...
Or am I mistaking?
It will only exclude the listed values and no other. Try the following and you'll see:
Set FinancialRelations = 100, 10000 ;
Load * Where not Match( relationNumber, $(FinancialRelations));
Load
'1'&repeat('0',recno()-1) as relationNumber
autogenerate 10;
HIC
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.
HIC