Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 ?
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
thanks for your replay, but i can't share my DB.
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)