I understand how Qlik handles what it calls 'JOINS'... but is it possible to do an actual join?
If I do the following:
And table 1 has the same 3 fields but only field 1 and 2 are primary keys that you want to merge on, this is possible in literally any other program/language because being able to define what fields you want to join on is an essential property of a join function. However, this isn't the case in Qlik, right? Because Qlik would interpret all 3 fields as primary keys. Is there something I'm missing? Is it possible to specify which field to join on?
Thanks for the response. But in the JOIN in my example, I would want to join field 3, just not as a primary key. In a LEFT JOIN for instance, Table 2 Field 3 values would replace null/blank values in Table 1 Field 3.
So for my case, I would have to load in Field 3 as a different name. My final table would then have Field 1, Field 2, Field 3_x, Field 3_y. And then I would have to create a calculated field: Field 3_final = IF Field 3_x IS NULL THEN Field 3_y ELSE Field 3_x.
The problem here is obvious. This is extremely inefficient if you have hundreds of fields you want to LEFT JOIN. My point is, these aren't joins by definition, they're concatenations and QLIK dev's should either change the expression or add in a feature to join on a field(s)
You would need to rename Field 3 in the table1, that way its going to join only on the basis of Field 1 and 2. There is no explicit way to define which fields you want to join on in the qlik, it automatically identifies based on the common field names.
In most cases are joins not the most suitable approach to add/match data between tables else mappings are often superior. This is related to the easiness to create them as well as to the not existing risks to unintentionally change the number of records if the data-relation isn't 1:1. And of course a mapping is much more performant.
The performance of mappings is usually as so much better as by the joins that you could also apply them within quite heavy nested ways. I use them nearly always and nesting of dozens mappings aren't seldom. In your case you may use something like this:
m: mapping load F1 & '|' & F2, F3 & '|' & F4 & '|' & F5 from t2;
set v = "subfield(applymap('$1', $2, '$3'), '|', $4)";
t3: load F1, F2, coalesce(F3, $(v(m, F1 & '|' & F2, 'default value', 1)), 'default') as F3 from t1;
By using parametrized variables as custom functions it becomes very handy and you may even go a step further with this logic - it's just a demo. I think there would be no lesser efforts possible regardless which join-approaches are in sql thinkable.
Of course by hundreds of fields and millions of records it will take some time. But you could just compare this method against the database (Qlik doesn't execute any sql itself else it always transferred the statement per driver to the db and get on this way the results back).
Hi Prashant, thank you for your response. The first 2 recommendations don't really solve the issue at hand. But number 2 definitely helps. I may look into executing a SQL query directly against my database... it may be the easier solution. Thanks