Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Stupid question (heat-related, I guess) on JOIN

Hi,

I'm just having a navigation problem in my own thoughts 😉

I have two tables, say A and B.

=> In table A, the key is unique

=> In table B, the key is not unique

=> I want to make them into one table using a LEFT JOIN

==>> D I have to JOIN table B to table A or the other way round now?

(I guess I'd have to join the table with the unique key to the one with the non_unique key, no?)

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

I don't think it matters, except that the left join will use the first table for the filtering. Are all the keys present in both files? If yes, then it does not matter. If no, then the first table would generally be the one with the most complete set of the join key(s).

Either way, for each key included in the final file, you will land up with the same number rows as there are in table B with that key value.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

14 Replies
sorrakis01
Specialist
Specialist

Hi,

I think you can create your join without problems, independence of the way, because you'll create a new table.

Regards

jonathandienst
Partner - Champion III
Partner - Champion III

I don't think it matters, except that the left join will use the first table for the filtering. Are all the keys present in both files? If yes, then it does not matter. If no, then the first table would generally be the one with the most complete set of the join key(s).

Either way, for each key included in the final file, you will land up with the same number rows as there are in table B with that key value.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
puttemans
Specialist
Specialist

I'd say table A into table B. That way you add through a unique key into a table with more possibilities.

The other way around would make you lose data you may not want.

datanibbler
Champion
Champion
Author

Hi jonathan,

okay. Every key in table B can be joined to a key in table A, I'm sure of that - it should in fact be so, so it was safe to use a LEFT KEEP before to mirror the time-wise limitation I have in table A because table B does not have a date_field.

If I understand you correctly, the general rule is that the primary table (the one that I put in brackets, that I join TO) should be the onewhere the key is non_unique, just as I have done it, is that right?

I will do it that way then, even if it doesn't really matter in this specific instance.

Best regards,

DataNibbler

jonathandienst
Partner - Champion III
Partner - Champion III

I don't think that 'uniquess' matters in the order and direction of the join. There may be performance differences between the two alternatives, but the final result will be the same.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
datanibbler
Champion
Champion
Author

Hi jonathan,

now I have something rather extraordinary which puzzles me a bit because I'm not so knowledgeable in the processes and stuff ...

Remaining with the example of table A and table B, I now have a situation where

- table A has approx. 275.000 records, but still the key is unique.

- table B has approx. 269.000 records, but the key is non_unique

=> I LEFT JOIN table A to table B now, so I go

    >> LEFT JOIN (table_B) ... <<

<=> The resulting table has the same nr. of records as table B, approx. 269.000. I wonder if that can be correct?

That is a question I cannot answer myself because I don't know much about the relation between the data in those tables. I'll ask someone from the Engineering_team who should know.

puttemans
Specialist
Specialist

Hi,

You join A into B with a left join, so you take all lines from table B (and only table B), and you add the variables from table A with the same unique key. It is then normal that you get the same number of lines as a result.

puttemans
Specialist
Specialist

Here's (part of) the reference document I always use in these instances.

Kind regards,

Johan

jonathandienst
Partner - Champion III
Partner - Champion III

Assuming that table A has a full set of the possible keys (in both A and B):

If you LOAD B and Left Join LOAD A, you will get the number of rows in B.

If you LOAD A and Left Join B, you will get something more than the number of rows in the original Table A.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein