Join on specific field

[pjp94]

I understand how Qlik handles what it calls 'JOINS'... but is it possible to do an actual join?

If I do the following: 

JOIN (table1)

Field 1

Field 2

Field 3

FROM (table2)

###########################

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?  

[rwunderlich]

Your understanding is correct, that all matching fieldnames are used in the join.  However, you can limit what fieldnames are used in the Load Statement. 

In your example above, I'll assume there is a "Field 4" in Table2 that you want to join to Table1.  Table2 contains a "Field 3" that you do not want to use in the join.  The syntax would be:

Join (Table1)
Load Field1, Field2, Field4, etc
Resident (Table2);

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[pjp94]

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)

[Digvijay_Singh]

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.

[PrashantSangle]

@pjp94 ,

As per your scenario there are 3 solutions.

1: using Qlik Scripting language

1.1 rename field 3 from 2nd table and do join like

Table1:

Load field1, field2,field3 from table1;

Join

Load field1,field2, field3 as t2_field3 from table2;

or

1.2: rename all fields and create composite key for joining. like

Table1:

Load field1&"_"&field2 as key,

field1 as t1_field1,

field2 as t1_field2,

field3 as t1_field3

from table1;

Join

Load field1&"_"&field2 as key,

field1 as t2_field1,

field2 as t2_field2,

field3 as t2_field3

from table2;

2: Qlik also support direct sql language while loading data from database. so you can use that query directly.

Regards,

Prashant Sangle

[rwunderlich]

"Table 2 Field 3 values would replace null/blank values in Table 1  Field 3"

Do you mean like a SQL "Coalesce(T1.Field3, T2.Field3)"?   You have to specify that explicitly, correct?  Or is there in automatic null replacement in SQL?

-Rob

[marcus_sommer]

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).

- Marcus    

[pjp94]

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

[pjp94]

I like the idea of mapping here. Since I'm new to Qlik, wasn't aware of this, but will give it a try. Thanks for your recommendation