Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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..
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:
Most important would be to not run out of RAM with your billions records ...
- Marcus
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?
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
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 :
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
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?
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
How would you "fill all relevant information" so that these lines are correctly "linked"?