
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
WHERE NOT EXISTS(...) clause returns only one row for each non-existing value
May 20, 2021 9:36:59 AM
Mar 27, 2019 10:38:05 AM
When using WHERE NOT Exists(<fieldName>) clause in a LOAD statement, the resulting table only contains a single row for each not-existing value of <fieldName>
For example, consider the following LOAD statements:
ID_table: LOAD * INLINE [ ID 1 ]; Data_table: LOAD * INLINE [ ID, Value 1, A 1, B 2, C 2, D 3, E 3, F ] WHERE NOT Exists(ID);
Expected result of Data_table after reload should be 4 rows with Value = C, D, E, F. However, actual result is that only 1 rows with Value = C,E are returned.
Environment:
- Qlik Sense Enterprise on Windows , all versions
- QlikView , all versions
Resolution:
This behavior is actually working as designed and is a common misconception - Where Not Exists is not exactly opposite with Where Exists in QlikView and Qlik Sense. The behavior is due to the line-by-line processing mechanism of Qlik engine. For example, in the above load statement:
- When loading line 3 (2, C), ID = 2 is not yet loaded before (i.e there is no match for this value in the in-memory symbol table for field "ID"). WHERE NOT Exists(ID) therefore returns TRUE, and this line is loaded
- After loading this line, value 2 is also written to the symbol table for field "ID"
- When it comes to line 4 (2, D), ID = 2 already exists in the symbol table as a result of step (2) above. WHERE NOT Exists(ID) now returns FALSE, therefore this line is not loaded.
The following blog post explains the behavior with a demonstration video: The Problem with Where Not Exists. Also refer to article How Is Data Stored In Qlik Sense And QlikView? for a discussion of how Qlik engine stores data in memory
The solution in this case is to rename the original ID field (for example ID1) and use WHERE NOT Exists(ID1, ID); instead. With this implementation, ID and ID1 are different fields, so they have different symbol tables and is not affected by the on-the-run update of the symbol table as shown above.
ID_table: LOAD ID as ID1; LOAD * INLINE [ ID 1 ]; Data_table: LOAD * INLINE [ ID, Value 1, A 1, B 2, C 2, D 3, E 3, F ] WHERE NOT Exists(ID1, ID);

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
But if I do this
WHERE NOT Exists(ID1, ID)
I lose my qvd load optimized 😢