More than one column in Exists condition

[#1]

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

[gandalfgray]

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!

[#3]

Use where not (exists (Day_No,Day_No)

and exists(Cntry_Cd,Cntry_Cd));

[#4]

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?

[jonathandienst]

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