Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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.
Hi Sadha,
but when i load LOCATION, its not override the values loaded from ENDERECOPARAPESQUISA?
no!,
exists() collects fieldvalues of a certain field independent of tables
Nice Robin,
In this case, how can i solve this to get the right output please?
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;
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;
Robin,
What the diference between NOT Exists() with 1 and 2 parameters?
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);