Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

Using Not Exists

Hi everyone,

I need some help please, because i get no value from OID_SYSTEM


thank you

I have this scenario:

***********************************************************************************************

Table ENDERECOPARAPESQUISA

Input:

OID_SYSTEM

6

24

30

Table LOCATION

Input:

OID_SYSTEM

24

30

And i expect this output:

Table ENDERECOS

Output:

OID_SYSTEM

6

***********************************************************************************************

***********************************************************************************************

Follow the code

BASE:

  LOAD

    OID_SYSTEM,

    ADDRESS_SYSTEM,

    ENDERECO

  FROM [lib://Dados/MI/RS/RS_SYSTEM.qvd] (qvd)

    where NOT ISNULL(OID_SYSTEM) and not isnull(ADDRESS_SYSTEM);

ENDERECOPARAPESQUISA:

  LOAD

  Distinct

  OID_SYSTEM,

  ENDERECO,

  ENDERECOPARAPESQUISA

  RESIDENT BASE where len(ENDERECO) > 6;

Drop table BASE;

LOCATION:

  LOAD

    OID_SYSTEM,

  ENDERECOPARAPESQUISA,

  lat,

  lng

  FROM [lib://Dados/MI/Enderecos/location.qvd] (qvd);

NoConcatenate

  ENDERECOS:

  LOAD

    OID_SYSTEM,

      ENDERECOPARAPESQUISA

    resident ENDERECOPARAPESQUISA

   WHERE NOT Exists(OID_SYSTEM);

DROP TABLE ENDERECOPARAPESQUISA;

DROP TABLE LOCATION;

1 Solution

Accepted Solutions
Anonymous
Not applicable

try

...

ENDERECOPARAPESQUISA:

  LOAD

  Distinct

  OID_SYSTEM,

  ENDERECO,

  ENDERECOPARAPESQUISA

  RESIDENT BASE where len(ENDERECO) > 6;

Drop table BASE;

LOCATION:

  LOAD

    OID_SYSTEM,

  OID_SYSTEM as OID_SYSTEM_X,

  ENDERECOPARAPESQUISA,

  lat,

  lng

  FROM [lib://Dados/MI/Enderecos/location.qvd] (qvd);

NoConcatenate

  ENDERECOS:

  LOAD

    OID_SYSTEM,

      ENDERECOPARAPESQUISA

    resident ENDERECOPARAPESQUISA

  WHERE NOT Exists(OID_SYSTEM_X,OID_SYSTEM);

drop field OID_SYSTEM_X;

View solution in original post

8 Replies
srchilukoori
Specialist
Specialist

You need to use two different field names for the "Where not exists" clause to work in this case as all the values in 'OID_System' are already loaded into memory.

eduardo_dimperio
Specialist II
Specialist II
Author

Hi Sadha,

but when i load LOCATION, its not override the values loaded from ENDERECOPARAPESQUISA?

Anonymous
Not applicable

no!,

exists() collects fieldvalues of a certain field independent of tables

eduardo_dimperio
Specialist II
Specialist II
Author

Nice Robin,

In this case, how can i solve this to get the right output please?

Anonymous
Not applicable

try

...

ENDERECOPARAPESQUISA:

  LOAD

  Distinct

  OID_SYSTEM,

  ENDERECO,

  ENDERECOPARAPESQUISA

  RESIDENT BASE where len(ENDERECO) > 6;

Drop table BASE;

LOCATION:

  LOAD

    OID_SYSTEM,

  OID_SYSTEM as OID_SYSTEM_X,

  ENDERECOPARAPESQUISA,

  lat,

  lng

  FROM [lib://Dados/MI/Enderecos/location.qvd] (qvd);

NoConcatenate

  ENDERECOS:

  LOAD

    OID_SYSTEM,

      ENDERECOPARAPESQUISA

    resident ENDERECOPARAPESQUISA

  WHERE NOT Exists(OID_SYSTEM_X,OID_SYSTEM);

drop field OID_SYSTEM_X;

eduardo_dimperio
Specialist II
Specialist II
Author

Hi,

Reading all the answers, i figure out how to solve. I just swap the order of tables.

LOCATION:

  LOAD

    OID_SYSTEM,

  ENDERECOPARAPESQUISA,

  lat,

  lng

  FROM [lib://Dados/MI/Enderecos/location.qvd] (qvd);

BASE:

  LOAD

  OID_SYSTEM,

  FROM [lib://Dados/MI/RS/RS_SYSTEM.qvd] (qvd)

    where NOT ISNULL(OID_SYSTEM)  AND NOT Exists(OID_SYSTEM);

DROP TABLE LOCATION;

eduardo_dimperio
Specialist II
Specialist II
Author

Robin,

What the diference between NOT Exists() with 1 and 2 parameters?

Anonymous
Not applicable

first parameter is the field from that the fieldvalues are collected,

second parameter is an expression from the current load statement

so Exists(OID_SYSTEM) is the same as Exists(OID_SYSTEM,OID_SYSTEM);