WHERE NOT EXISTS(...) clause returns only one row for each non-existing value

[Sonja_Bauernfeind]

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:

1. 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
2. After loading this line, value 2 is also written to the symbol table for field "ID"
3. 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);

[axnvazquez]

But if I do this 

WHERE NOT Exists(ID1, ID)

I lose my qvd load optimized 😢