Understanding EXISTS() function - Qlikview vs SQL/RDBMS

[#1]

I read this great article which explains how some features of qlikview might be unintuitive - http://luciancotea.wordpress.com/2013/08/02/qlikview-against-intuition/

Consider this -

TABLE1:
LOAD * INLINE [
    F1, F2
    1, a
    2, b
    3, c
];

LOAD * INLINE [
    F1, F2
    4, d
    4, e
]
WHERE NOT Exists(F1)


;

He says that "The last line from second table (4, e) will not be loaded despite the fact that 4 does not exists in TABLE1."

If same logic was written in SQL for DB, then last line would also be loaded. Actually, I think SQL seems unintuitive here. 

Then the author proceeds to create a copy of key F1 to load last line, just like SQL would.

TABLE1:
LOAD *, F1 as F3 INLINE [
    F1, F2
    1, a
    2, b
    3, c
];

concatenate
LOAD * INLINE [
    F1, F2
    4, d
    4, e
]
WHERE NOT Exists(F3, F1);

DROP FIELD F3;

My question is - Why create a copy of the column F1 ? Why can't we simply check if the COMBINATION of F1,F2 in Table2 does not exist in Table1 as shown below ? Why do I have to create a copy of a key ? What is the advantage ?

TABLE1:

LOAD * INLINE [

    F1, F2

    1, a

    2, b

    3, c

];

LOAD * INLINE [

    F1, F2

    4, d

    4, e

]

WHERE NOT Exists(F1,F2)

;

An extra thing - This same logic is used for a much bigger load in the Qlikview 11 for developers book. I wonder why.

See page 289 or so.

Thanks in advance.

[#2]

Hi John, I would have simply done a full outer join as attached.

Thanks,

Ram

[barryharmsen]

The Exists() function only checks a single field, so that is why you need to concatenate two or more fields together into a single field if you want to compare against the combinations of multiple fields. In this case, a join would work as well.

In the book, we used Where NOT Exists() on a link table to prevent duplicate records in the link table. My thinking around this subject has changed somewhat since the publication of the book, you can find an easier solution here: DISTINCT can be deceiving » The Qlik Fix! The Qlik Fix!

[DavidFoster1]

I would tend to do a blind concatenate into a temporary table and then do a distinct load into a final table to deduplicate the table rows.

This approach takes more time, but is unamibiguous.

[barryharmsen]

Hi David Foster

Check the "DISTINCT can be deceiving" post above. You can basically do it all in one pass by simply doing a LOAD DISTINCT.

Kind regards,

Barry

[#6]

Barry, Please consider joining Stackoverflow if you are not there already. There are very few QlikView experts there. SO is simpler and easier to use compared to this clunky forum. I am on a one man mission to end this forum.

Thanks

[Anonymous]

John,

The exists() function checks if a value of the field (first parameter of the exist function) has been loaded anywhere in the script.  This parameter is always a field, never an expression.  The exists in the second load compares existing F3 values against incoming F1 values here:
WHERE NOT Exists(F3, F1);

You suggestion about combination F1 and F2 is not going to work the same way because it is logically different.  And, you still must use field as the first parameter, can't avoid F3 here:

TABLE1:
LOAD *, F1&F2 as F3 INLINE [
    F1, F2
    1, a
    2, b
    3, c
];

concatenate
LOAD * INLINE [
    F1, F2
    4, d
    4, e
]
WHERE NOT Exists(F3, F1&F2);

The logical difference is easy to see if change data a little, add another line into the 1st table:
4, z

The example with (F1 as F3) is going to eliminate both record from the table 2, because there is F3=4 in table 1
The combination logic will keep all the data because all combinations are unique in this case.

Hope it helps a little...

Regards,
Michael

[#8]

Thanks.

Michael, please consider joining Stackoverflow if you are not there already. There are very few QlikView experts there. SO is simpler and easier to use compared to this clunky forum. I am on a one man mission to end this forum.

Thanks

[luciancotea · Accepted solution]

Hi,

I'm glad you liked my article. Now, to answer your questions:

Why create a copy of the column F1 ?

We need a temporary, fixed copy of the F1 field to use as reference in order to reach our goal (SQL-like behavior). In QlikView, Exists() function will check the F1 field for every imported row.

Why can't we simply check if the COMBINATION of F1,F2 in Table2 does not exist in Table1 as shown below ?

Remember, this is an example to illustrate a behavior. F2 is just extra info, cant use it.

Imagine you have a historical invoices details table with 30 columns, and you need to concatenate yesterday invoices (you have several lines per invoice). If you use the Exists() function like in SQL, you will import only the first line of every invoice.

What is the advantage ?

Advantage as to.... what?