Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Exclude a list of relations

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?

Tags (2)
1 Solution

Accepted Solutions
Employee
Employee

Re: Exclude a list of relations

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 Replies
Employee
Employee

Re: Exclude a list of relations

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

Not applicable

Re: Exclude a list of relations

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?

Employee
Employee

Re: Exclude a list of relations

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

Not applicable

Re: Exclude a list of relations

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!

Employee
Employee

Re: Exclude a list of relations

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

Community Browser