Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor II

Matching values of 2 tables

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?

1 Solution

Accepted Solutions
eduardo_dimperi
Valued Contributor II

Re: Matching values of 2 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 !

7 Replies
atkinsow
Valued Contributor II

Re: Matching values of 2 tables

Try ApplyMap

eduardo_dimperi
Valued Contributor II

Re: Matching values of 2 tables

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.

Exists1.JPG

atkinsow
Valued Contributor II

Re: Matching values of 2 tables

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

Re: Matching values of 2 tables

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

Re: Matching values of 2 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 !

atkinsow
Valued Contributor II

Re: Matching values of 2 tables

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;

atkinsow
Valued Contributor II

Re: Matching values of 2 tables

I'm glad you found your answer.