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

More than one column in Exists condition

Hi,

I need to compare more than one column using exists function...

Fact:
//To get the new records
LOAD Day_No,
     Cntry_Cd,
      Sales,
     Insert_Day_No

FROM
POC.xls
(biff, embedded labels, table is Exists2$)
where Insert_Day_No>1;

concatenate

//to get the previous day's records
LOAD Day_No,
     Cntry_Cd,

     Sales,
     Insert_Day_No

FROM
POC.xls
(biff, embedded labels, table is Exists1$)
where not exists (Day_No,Day_No)

and not exists(Cntry_Cd,Cntry_Cd);

I need to check whether the combination of Day_No and Cntry_Cd is available in the resultset of the upper query.

But this is not working as expected...Please help me on this....

4 Replies
gandalfgray
Valued Contributor

More than one column in Exists condition

You need to concatenate the two columns to be compared.

A good idea is also to include a "special" character between the concatenation

to avoid that the combination of Day_No "1" and Cntry_Cd "21" is confused with the combination of Day_No "12" and Cntry_Cd "1".

So something like this will do:

Fact:

//To get the new records

LOAD Day_No,

     Cntry_Cd,

      Sales,

     Insert_Day_No,

     Day_no&'¤'&Cntry_Cd As ComboKey

FROM

POC.xls

(biff, embedded labels, table is Exists2$)

where Insert_Day_No>1;

concatenate

//to get the previous day's records

LOAD Day_No,

     Cntry_Cd,

     Sales,

     Insert_Day_No,

     Day_no&'¤'&Cntry_Cd As ComboKey

FROM

POC.xls

(biff, embedded labels, table is Exists1$)

where not exists (ComboKey,Day_no&'¤'&Cntry_Cd);

Drop Field ComboKey; // If you don't want to use it anymore

Hope this helps!

Not applicable

More than one column in Exists condition

Use where not (exists (Day_No,Day_No)

and exists(Cntry_Cd,Cntry_Cd));

Not applicable

Re: More than one column in Exists condition

did you mean

"not (exists (Day_No,Day_No) 

and

not exists(Cntry_Cd,Cntry_Cd));"?

i'm not sure it is correct.

regarding the ComboKey - is it the best practice?

MVP
MVP

Re: More than one column in Exists condition

The combokey and the "exists(..) and exists(..)' give different results, so the choice depends on what you require.

The combokey tests for the existence of the combination of values of field1 and field2. This test is correct when the combinations are important (ie field1 and field2 are related in some way, however loosely), and I expect would be the more common requirement.

The separate exists clauses test for existence of a value of field1, regardless of the value of field2 and the the existence of a value of field2 regardless of field1. This test is correct if field1 and field2 need to be tested individually, and there is no direct relationship between them.

Hope I made that clear ...

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser