topic Re: Join in QlikView

Join

master_student — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have two tables budget and DA that have 2 columns in commun CDR and LB, a synthetic table was creates automatically by qlikview

the result of that join is only the commun values will appear? because I need all values of those fields in the two tables.

Thanks

Re: Join

andrespa — Wed, 25 May 2016 09:53:03 GMT

Hi, you can try concatenate between both tables.

Re: Join

Anonymous — Wed, 25 May 2016 09:55:09 GMT

Instead of join use Concatenate .

Re: Join

master_student — Wed, 25 May 2016 09:55:37 GMT

Could you explain to me how qlikview do the join by default?

Thanks

Re: Join

qlikview979 — Wed, 25 May 2016 09:57:04 GMT

Hi,

you can try with concatenate. or else share your data

Regards,

Mahesh.

Re: Join

ramasaisaksoft — Wed, 25 May 2016 09:59:02 GMT

Hi Master,

By default QV fallows Full outer Join & Concatenate

Re: Join

Anonymous — Wed, 25 May 2016 10:00:25 GMT

Follow this Attachment . It is really helpful

Re: Join

andrespa — Wed, 25 May 2016 10:03:40 GMT

QW by default do a full outer Join, wich is what concatenate does and what probably you would need.

You could read this one, is a pretty good article about it.

Understanding Join, Keep and Concatenate

Hope it helps,

Andrés

Re: Join

master_student — Wed, 25 May 2016 10:03:43 GMT

noConcatenate

SUIVI_DA:

load

LB,

LBopex,

CDR,

NUM_DA,

Mnt DA

resident xxxx;

Budget:

load

LB,

LBopex,

budget po

RESIDENT ccc;

I thought in qlik when we have the same LB and CDR, QV wil put theme in the same line but see below :

Re: Join

andrespa — Wed, 25 May 2016 10:06:07 GMT

That's because even if those two fields have the same value, DA_STATUS, and DA_NUM are different ones.

Re: Join

master_student — Wed, 25 May 2016 10:07:41 GMT

is there anyway to correct that?

thanks

Re: Join

MindaugasBacius — Wed, 25 May 2016 10:09:55 GMT

Use one fact table if both tables are needed:

Data :

load

LB,

LBopex,

CDR,

NUM_DA,

Mnt DA,

SUIVI_DA as Table

resident xxxx;

Concatenate

load

LB,

LBopex,

budget po,

Budget as Table

RESIDENT ccc;

Re: Join

Anonymous — Wed, 25 May 2016 10:11:57 GMT

Hi,

When I look at your script, I can see that only field 'LB' is in common, not 'LB' and 'CDR' :

noConcatenate

SUIVI_DA:

load

LB,

LBopex,

CDR,

NUM_DA,

Mnt DA

resident xxxx;

Budget:

load

LB,

LBopex,

budget po

RESIDENT ccc;

Can you share your datamodel with us ?

Re: Join

andrespa — Wed, 25 May 2016 10:14:19 GMT

Use concatenate

SUIVI_DA:

load

LB,

LBopex,

CDR,

NUM_DA,

Mnt DA

resident xxxx;

CONCATENATE

Budget:

load

LB,

LBopex,

budget po

RESIDENT ccc;

Re: Join

qlikview979 — Wed, 25 May 2016 10:17:41 GMT

Hi,

You can use Concatenate

Data :

load

LB,

LBopex,

CDR,

NUM_DA,

Mnt DA,

SUIVI_DA as Table

resident xxxx;

Concatenate

load

LB,

LBopex,

budget po,

Budget as Table

RESIDENT ccc;

Re: Join

master_student — Wed, 25 May 2016 10:22:57 GMT

I use the concatenate option and still have the same pb. this is because as you said before qv by defaut concatenate tables..

Re: Join

andrespa — Wed, 25 May 2016 10:26:14 GMT

Well, you could go for a key. Something like this might be useful:

Data :

load

LB &'_'&LBopex AS KEY,

LB,

LBopex,

CDR,

NUM_DA,

Mnt DA,

SUIVI_DA as Table

resident xxxx;

LEFT JOIN

load

LB &'_'&LBopex AS KEY,

LB,

LBopex,

budget po,

Budget as Table

RESIDENT ccc;

Re: Join

MindaugasBacius — Wed, 25 May 2016 10:33:12 GMT

Simple use left join without Key. By default the key is going to be LB and LBopex fields.

Re: Join

master_student — Wed, 25 May 2016 10:36:50 GMT

I did as you recommended but the column comming from the budget file was removed

Re: Join

MindaugasBacius — Wed, 25 May 2016 10:46:17 GMT

Try Outer Join then.