Problem with Exists Condition

[#1]

This could be silly question but not getting through …

Exception:LOADANALYSISFROM
excption.xls
(biff,embeddedlabels,tableisSheet1$);

Product:LOADPRODUCT_CODE,
     ANALYSIS FROM
product.xls
(biff,embeddedlabels,tableisSheet1$) whereExists(ANALYSIS);  // whereNotExists(ANALYSIS); 

Which gives me correct output… but Not Exists doesn’t give me correct answer!! L

[orital81]

Hi GM, Please clarify - what exactly are you trying to get here,

is it Products that does not have the exception analysis code ?

If that is the case, see the attached qvw file for solution.

[#3]

Thank you for this workaround Ori, Left Join will do the trick here but wanted to see how it works with EXISTS condition.

Is there a Issue with Exists Condition..!!

[swuehl]

GM, what are you expecting do get when using

case a)

...

where Exists(ANALYSIS);

case b)

...

where Not Exists(ANALYSIS);

I personally don't see an issue with the exists function, unless it is just not working as you expect, but I don't know what you expect..

[orital81]

GM,

In common SQL, "not exists" will work when joining 2 tables (in this case you will need a sub-select querry)

In QV, "not exists" removes unwanted records when you concatenate 2 tables, but not when joining 2 tables.

As far as I know, the best way is usingleft join as in my example.

[#6]

If you see my attached QVW file, You will see that after using not Exists i am getting only two Product Code. Which mean Not Exists return only Two records from Loading Table ( Products ). and it is wrong as it supposed to return all the products belongs to  ACF, SMI Analysis Type.

Wherein, I tried with Exists. and it is giving all possible product list from Analysis Type mentioned in Exception Table.

Hope i conveyed my expectation..

[#7]

Thx Ori,

Well, if we have a set of values loaded in RAM and exclude those values in subsequent load table(S), Left join would be inconvenient way, however if we directly use Not Exists would handle this smartly.

Do not know but logically NOT Exists Should work.. May be m worng.

Regards,

[swuehl · Accepted solution]

GM,

as Ori Tal said, exists function in an QlikView Load behaves a little different from the SQL exists (requiring a subquery), since it just takes a fieldname as Input.

In your above sample, after the first load the field ANALYSIS has 3 values (ALTAMOD, SWISSAR, SAPIL).

Then you do a second load using 'where not exists (ANALYSIS);'.

For each record read in, the second load will check if the current value for ANALYIS already exists in the field.

So no records containing ALTAMOD, SWISSAR, SAPIL will result in a record in the output table (i.e. data in the QV data model). And only the first record with values ACF resp SMI will pass, since for any subsequent record, the field ANALYSIS already shows these values (yes, the field is updated during the load).

So, considering the differences mentioned, I see no flaw in the use of the not exist function.

And yes, you can just use your two loads with the not exist functions to get what you want (assuming that you don't need your exception table linked to your product table afterwards, which isn't useful anyway, since you won't have matching values).

Either use:

Directory;

LOAD ANALYSIS

FROM

Exception.qvd

(qvd);

Directory;

LOAD ANALYSIS as ProductAnalysis,

     PRODUCT_CODE

FROM

Product.qvd

(qvd) where not Exists(ANALYSIS);

i.e. just rename the field name in the second load, so you won't add values to field ANALYSIS in the second load (preventing the first-record-only-issue), or if you prefer to keep the original field name for your Product Table:

Directory;

LOAD ANALYSIS as ANALYSIS_EX

FROM

Exception.qvd

(qvd);

Directory;

LOAD ANALYSIS,

     PRODUCT_CODE

FROM

Product.qvd

(qvd)

where not Exists(ANALYSIS_EX,ANALYSIS);

i.e. renaming the field that keep the exception values, then you need to use the two-parameter version of the exists function.

Hope this helps,

Stefan

[#9]

Make sense Stefan, thx a ton for briefing.