Link-table with multiple keys

[Anonymous]

hi,

I have data model with a link table for get rid of synthetic keys. (Qlik sense)

I use "outer join" in that table to keep all the information i want.

My probleme is that i don't realy understand how the order of the instructions influences the result of the compilation.

For exemple :

load all my table...

then

LinkTable:

Load Distinct `CIFEID-CIFID-ECCID-autre_CMA_UID`,

CIFEID,

CIFID,

ECCID

Resident [T1];

outer Join

Load Distinct `SPAID-CIFID-ECCID-autre_CMA_UID`,

SPAID,

CIFID,

ECCID,

`autre_CMA_UID`,

Resident [T2];

outer Join

Load Distinct `FID-CANDID-CIFID`,

FID,

CANDID,

CIFID

Resident [T3];

outer join

Load Distinct `ID-FID-EOID-CANDID`,

CAID,

AEID,

FID,

EOID,

CANDID

Resident [T4];

outer Join

Load Distinct `CIOID-EOID-CANDID`,

CIOID,

CANDID,

EOID

Resident [T5];

outer join

Load distinct `FID-KID_for-UID`,

FID,

KID_for,

UID

Resident [T6];

Outer Join

Load Distinct `EOID-EID-ECONID-KID`,

EOID,

EID,

ECONID,

KID_ent

Resident [T7];

I don't have the same result when join the T5 just after the T3 and i don't know why.

Someone have an explanation ?

Thanks in advance for your help,

Amin.

[YoussefBelloum]

Hi,

Maybe there is something else in your model that cause this difference.. ? sinse you're performing a full join I think that the order of the joins after the first load should not modify the result..

would you be able to share some details? sample app or sample data ?

[felipedl · Accepted solution]

Hi Amin,

Your joins will have different keys to be bound once you do the following joins:

/****** Case 1, T3 join T4 and join T5 ******/
outer Join
Load Distinct `FID-CANDID-CIFID`,
FID,
// Joined field with T4

CANDID,
//
CIFID
Resident [T3];

outer join
Load Distinct `ID-FID-EOID-CANDID`,
CAID,
AEID,
FID,
EOID,
// Joined field with T3
CANDID
//

Resident [T4];

outer Join
Load Distinct `CIOID-EOID-CANDID`,
CIOID,
CANDID,
// Joined field with resulting table above
EOID
Resident [T5];

/****** Case 2, T3 join T5 and join T4 ******/

So, if you alter the positions of the joins, like so:

outer Join
Load Distinct `FID-CANDID-CIFID`,
FID,
CANDID,
CIFID
Resident [T3];

outer Join
Load Distinct `CIOID-EOID-CANDID`,
CIOID,
CANDID,
// Joined field with T3
EOID
//

Resident [T5];

outer join
Load Distinct `ID-FID-EOID-CANDID`,
CAID,
AEID,
FID,
// Joined fields with the resulting table above
EOID,
CANDID
//

Resident [T4];

In this case, the joins are indeed different, since the joining fields are different when you alter the positions, giving more "information" for the join on the second case

[Anonymous]

thanks for your replay, but i can't share my DB.

[Anonymous]

Thank you for your explanation, I understand better what's going on.

I actually have more information with the second case. Qlik sense take also 1 more minute to load the data ( my T5 is quite big)