Joining 2 tables using 2 composite Keys problem!

[OmarBenSalem]

Hi all @sunny_talwar , @hic  , @Gysbert_Wassenaar , @swuehl , @jagan , @Clever_Anjos , @marcus_sommer , @tresesco , @rwunderlich ..

I've once faced a harsh problematic when I was trying to join 2 tables using 2 composite Keys while all of the values were not always available..

I've found a solution using an "index searching generic script" and have shared the script to the rest of the community (please READ IT to further understand my request) : https://community.qlik.com/t5/Qlik-Sense-Documents/Search-for-occurances-Generic-Script-for-Qlik-by-...

To resume : 

- Table 1 had a composite Key1 (Exp: |xx|02| )

- table 2 had another Key2 (Exp :|xx|1919|15|02|)

The goal is to join these 2 values since the xx and 02 from Key1 exists in Key 2 :|xx|1919|15|02|.

While it really works.. using my method, the table that search for the occurances will contain ALL OF THE POSSIBLE COMBINATIONS (maybe BILLIONS of lines..). This could affect the app's performance.

The question is : does ANYONE has another/BETTER way to do so without affecting the app's performance?

Thank you very much!

Omar BEN SALEM

[marcus_sommer]

I would use one or several - maybe nested - mappings. Mappings are fast, have no risks to change the number of records, provide a default-value for a non-matching and the possibility to calculate directly with the results. Also the creation of the mapping-tables is usually quite easy - just loading two columns from a single source (there are exceptions to this restriction but IMO they aren't really practically) - and such steps might be also outsourced in some layers before (means the pure creation and storing as qvd).

- Marcus  

[OmarBenSalem]

Still can't understand how..

Could you please show me how to actual link these 2 tables?

Table1:
load * Inline [
MG_GROUP, MG_NO, SUPP_CODE, PLANT_CODE, QUANTITY
AA, 1, SUP1, X, 300
AB, 2, SUP2, Y, 400
];

Table2:
load * Inline [
MG, NO, SUPP, PLANT, KM
,1, SUP1,, 10
, , SUP1, ,20
,,, X, 15
,,SUP2,,
];

[marcus_sommer]

Table2 seems not to contain really useful data and therefore a merging/associating of it with any other tables seems not to be sensible. Therefore my above suggestion to prepare this table with various measurements. The relevant data must be there because otherwise there would be also nothing exists with which you may create those composite keys with which this thread started.

- Marcus 

[OmarBenSalem]

It could be sthing like this? (it only works when all fields in table1 are always filled..)

Table1:
load *, AutoNumberHash128(MG_GROUP&'|'&MG_NO&'|'&SUPP_CODE&'|'&PLANT_CODE) as %KEY Inline [
MG_GROUP, MG_NO, SUPP_CODE, PLANT_CODE, QUANTITY
AA, 1, SUP1, X, 300
AB, 2, SUP2, Y, 400
AB, , SUP2, , 200
];

//MG_GROUP Mapping tables

Map_MG_GROUP_MG_NO:
mapping load
MG_GROUP, MG_NO
Resident Table1;

Map_MG_GROUP_SUPP_CODE:
mapping load
MG_GROUP, SUPP_CODE
Resident Table1;

Map_MG_GROUP_PLANT_CODE:
mapping load
MG_GROUP, PLANT_CODE
Resident Table1;

//MG_NO Mapping tables

Map_MG_NO_MG_GROUP:
mapping load
MG_NO, MG_GROUP
Resident Table1;

Map_MG_NO_SUPP_CODE:
mapping load
MG_NO, SUPP_CODE
Resident Table1;

Map_MG_NO_PLANT_CODE:
mapping load
MG_NO, PLANT_CODE
Resident Table1;

//SUPP_CODE Mapping tables

Map_SUPP_CODE_MG_GROUP:
mapping load
SUPP_CODE, MG_GROUP
Resident Table1;

Map_SUPP_CODE_MG_NO:
mapping load
SUPP_CODE, MG_NO
Resident Table1;

Map_SUPP_CODE_PLANT_CODE:
mapping load
SUPP_CODE, PLANT_CODE
Resident Table1;

//PLANT Mapping tables

Map_PLANT_CODE_MG_GROUP:
mapping load
PLANT_CODE, MG_GROUP
Resident Table1;

Map_PLANT_CODE_SUPP_CODE:
mapping load
PLANT_CODE, SUPP_CODE
Resident Table1;

Map_PLANT_CODE_MG_NO:
mapping load
PLANT_CODE, MG_NO
Resident Table1;

Table2:
load * Inline [
MG, NO, SUPP, PLANT, KM
,1, SUP1,, 10
, , SUP1, ,20
,,, X, 15
,,SUP2,, 50
];

NoConcatenate
Link:
load if(len(trim(MG))<>0,MG) as MAP_MG,
if(len(trim(MG))<>0,ApplyMap('Map_MG_GROUP_MG_NO',MG)) as MAP_NO,
if(len(trim(MG))<>0,ApplyMap('Map_MG_GROUP_SUPP_CODE',MG)) as MAP_SUPP,
if(len(trim(MG))<>0,ApplyMap('Map_MG_GROUP_PLANT_CODE',MG)) as MAP_PLANT
,KM
Resident Table2;

Concatenate
load if(len(trim(NO))<>0,NO) as MAP_NO,
if(len(trim(NO))<>0,ApplyMap('Map_MG_NO_MG_GROUP',NO)) as MAP_MG,
if(len(trim(NO))<>0,ApplyMap('Map_MG_NO_SUPP_CODE',NO)) as MAP_SUPP,
if(len(trim(NO))<>0,ApplyMap('Map_MG_NO_PLANT_CODE',NO)) as MAP_PLANT
,KM
Resident Table2;

Concatenate
load if(len(trim(SUPP))<>0,SUPP) as MAP_SUPP,
if(len(trim(SUPP))<>0,ApplyMap('Map_SUPP_CODE_MG_GROUP',SUPP)) as MAP_MG,
if(len(trim(SUPP))<>0,ApplyMap('Map_SUPP_CODE_MG_NO',SUPP)) as MAP_NO,
if(len(trim(SUPP))<>0,ApplyMap('Map_SUPP_CODE_PLANT_CODE',SUPP)) as MAP_PLANT
,KM
Resident Table2;

Concatenate
load if(len(trim(PLANT))<>0,PLANT) as MAP_PLANT,
if(len(trim(PLANT))<>0,ApplyMap('Map_PLANT_CODE_MG_NO',PLANT)) as MAP_NO,
if(len(trim(PLANT))<>0,ApplyMap('Map_PLANT_CODE_SUPP_CODE',PLANT)) as MAP_SUPP,
if(len(trim(PLANT))<>0,ApplyMap('Map_PLANT_CODE_MG_GROUP',PLANT)) as MAP_MG
,KM
Resident Table2;

// left join(Table1)
Sourcing :
load Distinct AutoNumberHash128(MAP_MG&'|'&MAP_NO&'|'&MAP_SUPP&'|'&MAP_PLANT) as %KEY
,MAP_MG //as MG_GROUP
,MAP_NO //as MG_NO
,MAP_PLANT //as PLANT_CODE
,MAP_SUPP //as SUPP_CODE
,KM Resident Link;

drop Table Link;

drop Table Table2;

Result :

[marcus_sommer]

Yes, it goes already a bit in the direction which I had meant. Whereby using fact-tables extracts to create the mappings would be rather the last source for it else many fields should be fillable by various dimension-tables. In this regard make sure that you really get all relevant tables/fields from the underlying data-base because it could save a lot of work. Especially if the preparing couldn't be always done with single-layer approach else multiple layers has to be implemented.

Beside this a mapping could be nested in several ways, for example:

m: mapping load Lookup, F1 & '|' & F2 & '|' & F3 from X;

and the access might be look like:

subfield(applymap('m', Lookup, 'Default'), '|', 2) as F2

Further you may use a logic like:

applymap('m1', Lookup, applymap('m2', Lookup, 'Default'))

and you may also concatenate multiple and/or different Lookup values within a single mapping by using composite Lookup values and/or using an order by within the mapping-load (a mapping returned always the first match and therefore with an appropriate ordering multiple matches could be included and fetched with a defined priority).

This means you may not necessary need dozens of mappings else the efforts could be reduced in some way. Of course applying such nested approaches will increase the complexity - so you may balance the efforts against the complexity.

But like above already hinted I wouldn't tend to join the prepared tables else I would try to concatenate them because joining and/or associating the tables won't always work if the data-relation isn't clear and/or there are any missing keys within the tables. The above shown preparing of the tables isn't enough to fetch also this kind of use-cases. A concatenated fact-table within a star-scheme data-model will instead work for the most scenarios and again it's much simpler to do.

- Marcus  

[OmarBenSalem]

Still can't find a better way to achieve this.. any help please? @sunny_talwar ?

[marcus_sommer]

Where is the problem?

• validating the raw-data
• cleaning / replacing any data-quality issues
• preparing the data-tables
• implementing an incremental load-approach within a multi-tier data-architecture
• designing suitable data-models
• building the data-models by any merging/linking approaches
• UI

From your description and shown examples it seems that the data-quality from your raw-tables is rather poor. The more worse they are the more efforts are needed to make something usefully from them. An easy way to solve the challenges is quite unlikely. Therefore my suggestion to go the long way and doing everything systematically from the beginning and not looking for shortcut and trying to make the second/third step before the first one - which usually lead rather to the opposite ... a quite tedious detour ... 

- Marcus

[OmarBenSalem]

The problem is that I have absolutely no control over the source data. Thus, I can't change it but rather find a better way to Link the 2 tables..

[OmarBenSalem]

@sunny_talwar still looking for a workaround..

[vchuprina]

Hi Omar, 

Regarding you example

- Table 1 had a composite Key1 (Exp: |xx|02| )

- table 2 had another Key2 (Exp :|xx|1919|15|02|)

 As I see keys in both tables are different, but if you try to join them it means that the values should be the same. I mean that you join OrderID to OrderID and OrderType to OrderType, so you know that the first value in Key1 and the first value in Key2 mean the same, and the second value in Key1 and the fourth value in Key2 also mean the same (see example below).

If so, maybe it makes sense just to create Key1 in table 2 based on Key2

'|'&SubField(Key2, '|', 2) &'|'& SubField(Key2, '|',-2)&'|' AS Key1

Regards,

Vitalii