Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to check if exists common data between two tables. For that i try 2 ways
//***************Using Inner Join
RS_BILLING:
Load
OID_SYSTEM
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
Inner Join(RS_BILLING)
LOAD
OID_SYSTEM,
OID_GROUP,
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
Output:
Load
Isnull(OID_SYSTEM) AS Check
resident (RS_BILLING);
//***************** Using Exists()
RS_BILLING:
Load
OID_SYSTEM
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
SISTEMA:
LOAD
OID_SYSTEM,
OID_GROUP,
If(Exists(OID_SYSTEM,), 'Yes', 'No') AS CHECK
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
In both cases the right output is Null, but i receive missing values. For that reason Isnull and other tools to work if Nulls doesn't work.
Any idea?
Wallo, I solved.
Its similar to your post.
SISTEMA:
LOAD
OID_SYSTEM,
OID_GROUP
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
CONCATENATE
Load
OID_SYSTEM
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
LEFT JOIN(SISTEMA)
Output:
Load
OID_SYSTEM,
if(Isnull(OID_GROUP),'No','Yes') AS Check
resident SISTEMA;
The diference its need to concatenate the two tables first and then use a left join on this new table. Thanks for the help !
Try ApplyMap
Hi Wallo, i already tried to and it not work,to use that i neet at least 3 fields in a table (that was the error message).
To be honest, i look again my code and realize that was inverted the tables, first is:
SISTEMA:
LOAD
OID_SYSTEM,
OID_GROUP
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
and after
RS_BILLING:
Load
OID_SYSTEM,
If(Exists(OID_SYSTEM,), 'Yes', 'No') AS CHECK
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
But still not working cause i receive to the same OID_SYSTEM YES AND NO. Its duplicated and i dont know why.
It's probably doing an autoconcatenate.
But if you go with a join, I think you'd want a left join,
RS_BILLING:
Load
OID_SYSTEM
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
LEFTJoin(RS_BILLING)
LOAD
OID_SYSTEM,
OID_GROUP,
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
Output:
Load
if(Isnull(OID_GROUP),'No','Yes') AS Check
resident (RS_BILLING);
I dont know if im doing this. How can i check?
About join not working too, but left join doesnt give me the intersection anyway between two tables.
Wallo, I solved.
Its similar to your post.
SISTEMA:
LOAD
OID_SYSTEM,
OID_GROUP
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
CONCATENATE
Load
OID_SYSTEM
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
LEFT JOIN(SISTEMA)
Output:
Load
OID_SYSTEM,
if(Isnull(OID_GROUP),'No','Yes') AS Check
resident SISTEMA;
The diference its need to concatenate the two tables first and then use a left join on this new table. Thanks for the help !
What is the key field or fields between the 2 tables? OID_SYSTEM?
Not sure I'm understanding what you want. Maybe this?
List:
Load
OID_SYSTEM
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
concatenate(List)
LOAD distinct
OID_SYSTEM
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
MapBillingLd:
OID_SYSTEM,
'Yes' as Match
From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);
MapRSLd:
LOAD
OID_SYSTEM
'Yes' as Match
FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]
(qvd)
WHERE OID_GROUP = 20 OR OID_GROUP = 27;
noconcatenate
Final:
load *,
applymap('MapBillingLd',OID_SYSTEM,'No') as BillingTableMatch,
applymap('MapRSLd',OID_SYSTEM,'No') as RSTableMatch
resident List;
I'm glad you found your answer.