Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The script -
TABLE1:
LOAD * INLINE [
F1, F2
1, a
2, b
3, c
];
LOAD * INLINE [
F1, F2
4, d
4, e
]
WHERE NOT Exists(F1)
;
I know SQL and the syntax for Qlikview's "where" confuses me. In "WHERE NOT Exists(F1)" F1 belongs to which table ?
In the above script, F1 refers to both the first and the second tables.
Actually the not exists in the second load statement checks if the value of F1 being loaded is already present in the data model, in this case Table1. Since 4 is not already present, both the rows will be added.
To be more clear it is like this.
where not exists ( F1, F1)
1st argument is the already loaded field
second argument is the field being loaded.
In case the field name is different we can use as follows:
where not exists ( F1, Field1). Since in your case the field name is the same the second argument can be skipped.
Thanks,
Ram
Attaching the help screen shot.
thanks. Where can i see rest of the documentation in screenshot, ie API ?
goto your qlikview desktop, press F1. type exists. You will get the documentation.
Thanks,
Ram
Check the Help documentation.
" Since 4 is not already present, both the rows will be added." That is incorrect. Only one row will be added. More on that later.
you are correct. I didn't notice. For both to be added, we need the condition slightly modified.
where not exists(F1) or not exists(F2).
Reason being we are checking for not exists(F1) and that works iteratively. We read the value of 4 for F1 and when we read the next row, since 4 is already available it didn't read it. Basically we can tweak this based on our needs.
Thanks,
Ram
here is the link - Understanding EXISTS() function - Qlikview vs SQL/RDBMS