Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
gabriel-spiteri
Contributor

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.

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

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 

View solution in original post

5 Replies
marcus_sommer

Just remove the NOT from the where-clause.

- Marcus

gabriel-spiteri
Contributor
Author

@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.

marcus_sommer

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 

gabriel-spiteri
Contributor
Author

@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

marcus_sommer

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