Exact working of JOIN in QlikView script?

[datanibbler]

Hi,

this is seemingly a very simple question, and more related to SQL syntax than to QlikView in particular, but the results in my current document confuse me a bit: What is the exact working of a JOIN implemented in a QlikView script?

When I specify, before a load_statement, a LEFT JOIN(

and specify, in brackets, the table that I want to join to, that should return the same nr. of records that I had in the table specified in brackets, shouldn't it?

Thanks a lot for clarifying!

Best regards,

DataNibbler

[alexandros17]

A left join works as follow (IN qlik and SQL)

Table a left join Table b

where fields of table a : id, val and field of tab b : id and val1

each id in tab a is taken and each id in tab b whose value is in tab a (repeated for all occurrences of tab b)

[sujeetsingh]

It should return same no. of record in brackets.

[datanibbler]

Hi,

that's exactly what I supposed - but it doesn't work that way. I have a table - table_A - that I want to join to a table named View_Pkg_Items. When I add

LEFT JOIN(View_Pkg_Items)

to the LOAD-Statement of table_A and delete the > Directory; < above it, then afterwards my table View_Pkg_Items  has about 10 times the nr. of records.

Do I have to add a WHERE clause? I don't think so - the one field that matches is the join-field anyway, so the LEFT JOIN should throw out all non-matching records?

P.S.: Is there maybe a difference in the order in which I join tables? There is another table that I also join via a LEFT JOIN to View_Pkg_Items, it's named Item_Master. I don't know if I can join this new table to Item_Master and the resulting table to View_Pkg_Items? Would that make a difference, and do I have to rearrange the tabs of my script in some way to achieve that?

[swuehl]

A JOIN or LEFT JOIN may increase the number of records.

If you are using

LEFT JOIN (TABLE1) LOAD A, B FROM TABLE2;

and your key is field A, you ensure that you won't find a tupel (A,B) in the resulting relation where value A doesn't exist in your original TABLE1. So if you have something like

TABLE1:

LOAD * INLINE [

A

1

3

];

LEFT JOIN (TABLE1)

LOAD * INLINE [

A, B

1,2

1,3

2,2

];

your resulting table will be

A,B

1,2

1,3

3, NULL

You will only find values in field A that already existed in the original TABLE1.

But your number of records increased from 2 to 3.

Hope this helps,

Stefan

[datanibbler]

Hi swuehl,

thanks for this hint! You're right, that is exactly my issue: The keyfield I'm using is not unique ("Perfect key") in the new table table_A that I want to join to the existing one - there are duplicate records (same keyfield, but different other_field), so those will be multiplied in the result table.

I only don't yet know how I can solve this.

P.S.: If the JOIN does not work, can I be certain that a regular association will produce a correct result? If not, that would be a mayor issue in my eyes as it would mean the associative logic of QlikView is generally not to be trusted, which would reduce its value to just about zero...

[#7]

Hi Friedrich,

Create a composite key in both the tables that would make this association a unique one.

It would solve your problem.

-BR,

Abhinava

[swuehl]

Please define 'correct result' (i.e. describe your input data a bit closer, also your expected outcome / requirements).

It seems to me that your tables have a 1:n relationship. The way QV joins these tables is not much different from SQL.

And remember, maybe you don't need to join these tables (like if you want to sum a field in TABLE2, NOT joining may return a correct result (depending on your requirements)).

I remember some blog posts from Henric, Rob, et.al.

Search this forum and the web for JOIN / CONCATENATE / Qlikkview. You should find something like this classic blog post:

http://qlikviewnotes.blogspot.de/2009/11/understanding-join-and-concatenate.html

[datanibbler]

Hi swuehl,

thanks! I will look into that asap.

I have worked with another software before that was also somehow based on SQL. I remember that for a LEFT JOIN to work, the relation should actually be n:1 - the key should be unique in my secondary table.

Between my main table "Shipped" and the masterdata table I associate with it (via a Package_ID), the relation is 1:n because in the masterdata table, I have one more detail level (items) below the "Package" level of the keyfield. That is all right because I need all items within the packages.

I tried to avoid further dimension (masterdata) tables linked only to the "primary dimension table" because that would contradict the ideal of a "star schema" in my data model.

Now, in that "primary dimension table", the item_id is not unique because one item can be in multiple packages. Hmm... but in the next table, "Item_Master", the keyfield says "Primary key" and by a quick check via the >COUNT(DISTINCT)< function I just confirmed that every item_nr exists only once in that table. So that one is okay, the LEFT JOIN should work - as it does, I had seen it before.

The next table, "Business_Plan", is another matter: There the keyfield is everything but unique - I have to find out what is the issue there. Maybe I have to include the date and use the latest (most current) record, I don't know.

However, I would very much like to understand what is the issue with the associations - if they only work correctly on a unique key, but QlikView creates them automatically even with a non-unique key, that's an issue...

Thanks a lot in any case!

Best regards,

DataNibbler

[calvindk]

Friedrich Hofmann wrote:

However, I would very much like to understand what is the issue with the associations - if they only work correctly on a unique key, but QlikView creates them automatically even with a non-unique key, that's an issue...

Thanks a lot in any case!

Best regards,

DataNibbler

They work correctly in both cases, as they link what is linkable.

Imagine a case where i have a bookstore and i want to see what books are being sold there.

I have bookstore_id and store info in that table.

In my other table i have bookstore_id, book_id and book info

Now they are linked 1:N exactly as intended as each store could be selling the same books.

Best wishes