
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Exists / Not Exists confusion
Hi All,
I working on a simple load script that need to exclude a set of records based on a list from another table.
PL:
LOAD * INLINE [
Exclude_G_L_Account_No
1650
1091
1381
1531
1532
];
CONCATENATE (PL)
LOAD
"Entry No_",
"G_L Account Name",
"G_L Account No_",
Income_Balance,
"Posting Date",
server_time_pl,
growth_area_pl,
eb_growth_area_pl,
CK_Fact_PL,
"Document Type",
LegalEnt
FROM [lib://Management Analysis - Data Model/P_L Full.qvd]
(QVD)
WHERE NOT EXISTS(Exclude_G_L_Account_No, [G_L Account No_]);
So the idea is to remove any records from the second table whose G_L Accout no is present in the first inline table.
However for some reason these records are not removed.
I don't understand what the issue might be.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe it's just a converting/formatting stuff because the inline content will be mostly strings. Therefore make the interpreting explicitly, for example with:
PL:
LOAD num(num#(Exclude_G_L_Account_No)) as Exclude_G_L_Account_No INLINE [
Exclude_G_L_Account_No
1650
1091
1381
1531
1532
];
- Marcus


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just remove the NOT from the where-clause.
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@marcus_sommer thanks however with just AND EXISTS no records are loaded, whilst with AND NOT EXISTS everything is loaded (unfiltered)
It's as if when the exists function is comparing the field for the inline table it never finds any match.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe it's just a converting/formatting stuff because the inline content will be mostly strings. Therefore make the interpreting explicitly, for example with:
PL:
LOAD num(num#(Exclude_G_L_Account_No)) as Exclude_G_L_Account_No INLINE [
Exclude_G_L_Account_No
1650
1091
1381
1531
1532
];
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@marcus_sommer yes that did it ... i had to convert both the value from the inline table and also the one i m loading from the QVD.
PL:
LOAD
num(num#(Exclude_G_L_Account_No)) as Exclude_G_L_Account_No
INLINE [Exclude_G_L_Account_No
1650
1091
1381
1531
1532
.
.
.
AND NOT EXISTS(Exclude_G_L_Account_No, num(num#("G_L Account No_")))
I am really curious to understand what is the difference of the types.
Thanks


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Qlik doesn't know data-types else values are interpreted as numeric or as strings or as dual(string, numeric). Now it depends on the context how a comparison is applied. In many case like a set analysis ... Field = {value} ... it compares against the string-interpretation unless you used operators like ... Field = {">=value"} ... then a numeric comparison is performed. Now in the case of exists() with mixed field-values it didn't match (it's similar to an Excel VLOOKUP which wouldn't also match).
Beside this you may simplify and optimize your approach with something:
PL: LOAD pick(recno(), 1650,1091,1381,1531,1532) as G_L Account No_ autogenerate 5;
CONCATENATE (PL)
LOAD fields ...
FROM [lib://Management Analysis - Data Model/P_L Full.qvd] (QVD)
WHERE NOT EXISTS([G_L Account No_]);
The PL will now contain numeric values and the exists() is used with a single parameter which means if not other processing is added the load would be optimized which is very useful by larger data-sets. But you need to give attention to the load-order because an exists() happens always against the fields and not fields within a certain table. Further a concatenating must go against an identically table-structure because otherwise it requires a processing again. Nearly each load-logic could be performed optimized ...
- Marcus
