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

[OmarBenSalem]

To further explain my issue with my "used method" :

Table 1 : 2.6M lines

Table 2 : 14 lines 

Index table : 88M lines...

Now imagine table2 containg 1M lines instead of 14..

[marcus_sommer]

Your use-case isn't complete clear and includes also further factors from your environment. Here a few things which I would consider if I had to do this job:

• applying an incremental load-approach
• using specialized key's - not |xx|02| against |xx|1919|15|02| else |xx|02| against |xx|02| - at least for merging these data
• converting the string-key's into numeric ones - it may not be trivial and maybe requiring additionally work with (multiple) string-to-number-mappings but it might be possible to nest multiple numbers without overlapping them
• replacing the join with a mapping
• exists-checks of the key's of all sources to each other - aim would be to reduce the number of records of the datasets on which the merging is really performed - of course it add's some overhead but optimized qvd-loads with a where (not) exists() are quite fast and the overall run-time may decrease 

Most important would be to not run out of RAM with your billions records ...

- Marcus

[OmarBenSalem]

Let me try to better explain what I'm aiming to do :

Table 1 :

MG_GROUP	MG_NO	SUPP_CODE	PLANT_CODE	QUANTITY	KEY1??
AA	1	SUP1	X	300	1
AB	2	SUP2	Y	400	2

Table 2 :

MG_GROUP	MG_NO	SUPP_CODE	PLANT_CODE	KM	KEY2??
	1	SUP1		10	1
		SUP1		20	1
			X	15	1
		SUP2			2

With this, 

The first line of table 1 should be "linked" to the first 3 lines of table 2 : why?

Table 1 : 

AA

1

SUP1

X

Table 2 :

in the first 3 lines, we have a combination of "mg_groupe, mg_no, supp_code or plant_code" within the line 1 of table 1

As for the 4th line of table 2 = link = line 2 table 2..

Hope this was.. clearer?

[marcus_sommer]

I'm not sure that I would try to join both tables else rather to concatenate them.

The multiple empty cells from table 2 look a bit odd. If this is also within the real world I could imagine that it's sensible to "adjust" it - maybe by filling missing values per mapping or with interrecord-functions and/or aggregating the records and of course removing/flagging invalid ones.

- Marcus

[OmarBenSalem]

Not trying to join.. but rather link them to know that informations of line1 of table1 is linked to the first 3 lines of table2..

If you had these 2 tables and you're trying to "link" them as I've described.. How would you do?

This is the only way I've found : 

https://community.qlik.com/t5/Qlik-Sense-Documents/Search-for-occurances-Generic-Script-for-Qlik-by-...

[marcus_sommer]

I doesn't meant to associate the tables within the data-model else to concatenate them, like:

t: load * from table1; concatenate(t) load * from table2;

- Marcus

[OmarBenSalem]

there is NO KEY (The final KEY??) in the shown example does not exist. If I concatenate them how do i know that the line 1 of table 1 with its quantity is linked to the other first 3 with km of each?

[marcus_sommer]

You may create this composite key like you mentioned by this approach, too. But probably it's not necessary to have such unique key for the most kind of views. Of course you need appropriate data within both tables to access them accordingly. This means filling all relevant information - like order/billing/shipping ID's, categories, dates and so on.

It might seem like a lot of work but I think it's regardless from the data-model (multiple fact-tables with or without link-tables or merging approaches like joining/mapping or concatenating) this work must be done anyway. No method will work without it and in my experience is concatenating the fact-tables the easiest way.

- Marcus

[OmarBenSalem]

How would you "fill all relevant information" so that these lines are correctly "linked"?