Matching values of 2 tables

[eduardo_dimperio]

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?

[Anonymous]

Try ApplyMap

[eduardo_dimperio]

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.

[Anonymous]

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

[eduardo_dimperio]

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.

[eduardo_dimperio · Accepted solution]

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 !

[Anonymous]

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;

[Anonymous]

I'm glad you found your answer.