"Where Not Exists" not working as expected

[qvdrago]

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

[PhanThanhSon · Accepted solution]

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);

TO 

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

Best regards Son