Exclude a list of relations

[#1]

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?

[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

[#3]

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

• $(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?

[hic · Accepted solution]

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

[#5]

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!

[hic]

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