Using Not Exists

[eduardo_dimperio]

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;

[srchilukoori]

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]

Hi Sadha,

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

[Anonymous]

no!,

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

[eduardo_dimperio]

Nice Robin,

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

[Anonymous · Accepted solution]

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]

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]

Robin,

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

[Anonymous]

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