Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Understanding EXISTS() function - Qlikview vs SQL/RDBMS

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.

1 Solution

Accepted Solutions
luciancotea
Specialist
Specialist

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?

View solution in original post

8 Replies
Not applicable
Author

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

Thanks,

Ram

barryharmsen
Luminary Alumni
Luminary Alumni

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

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
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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

mov
Employee
Employee

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

Not applicable
Author

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

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?