Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor 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;

Tags (2)
1 Solution

Accepted Solutions
roharoha
Valued Contributor III

Re: Using Not Exists

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;

8 Replies
srchilukoori
Valued Contributor

Re: Using Not Exists

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_dimperi
Valued Contributor II

Re: Using Not Exists

Hi Sadha,

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

roharoha
Valued Contributor III

Re: Using Not Exists

no!,

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

eduardo_dimperi
Valued Contributor II

Re: Using Not Exists

Nice Robin,

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

roharoha
Valued Contributor III

Re: Using Not Exists

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_dimperi
Valued Contributor II

Re: Using Not Exists

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_dimperi
Valued Contributor II

Re: Using Not Exists

Robin,

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

roharoha
Valued Contributor III

Re: Using Not Exists

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

Community Browser