topic Re: Exclude a list of relations in QlikView

Exclude a list of relations

Tue, 19 Feb 2013 09:16:41 GMT

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?

Re: Exclude a list of relations

hic — Tue, 19 Feb 2013 10:37:51 GMT

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

Re: Exclude a list of relations

Tue, 19 Feb 2013 11:00:29 GMT

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?

Re: Exclude a list of relations

hic — Tue, 19 Feb 2013 11:38:41 GMT

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

Re: Exclude a list of relations

Tue, 19 Feb 2013 11:51:17 GMT

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!

Re: Exclude a list of relations

hic — Tue, 19 Feb 2013 12:07:05 GMT

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