"Where Not Exists" not working as expected

qvdrago — Wed, 19 Mar 2025 22:11:20 GMT

Hi,

please watch this script:

---------

KOrd31:

Load *

From [lib://Database:DataFiles/KOrd31_demo.qvd] (qvd);

// create a table with distinct BTKBCH field

TabBch:

Load distinct

BTKBCH

resident KOrd31

where BTKBCH > ' ';

// load description

Left join

Load

BTKBCH,

Description

From [lib://Database:DataFiles/BchTab_demo.qvd] (qvd);

// only 2 codes match, i'd like to avoid null descriptions

// ENHANCE MISSING CODES

Concatenate(TabBch)

Load DISTINCT

BTKBCH,

BTKBCH as MissingCode,

'*** Missing code ***' as Description

resident KOrd31

where not exists(MissingCode, BTKBCH);

drop Field MissingCode;

Exit Script;

---------

All the records are loaded again in table TabBch, not only missing codes. What's wrong?

Thank you, very much.

Antonio

Re: "Where Not Exists" not working as expected

PhanThanhSon — Mon, 26 Feb 2024 16:21:21 GMT

Hi,

I suggest swapping the order of the fields in your "WHERE NOT EXISTS" query and renaming the fields in your first three tables from BTKBCH to BTKBCH2:

Change from:

where not exists(MissingCode, BTKBCH2);

where not exists(BTKBCH2, MissingCode);

I believe this adjustment will address the issue you're encountering. For further clarification, you can refer to the following post:

https://community.qlik.com/t5/Official-Support-Articles/WHERE-NOT-EXISTS-clause-returns-only-one-row-for-each-non/ta-p/1716931

Best regards Son

topic Re: "Where Not Exists" not working as expected in Data Movement & Streaming

"Where Not Exists" not working as expected

Re: "Where Not Exists" not working as expected