Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

Exact working of JOIN in QlikView script?

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

1 Solution

Accepted Solutions
calvindk
Contributor III

Re: Exact working of JOIN in QlikView script?

And here is a good link explaining the join, keep and concatenate options you have:

http://community.qlik.com/thread/39177

12 Replies

Re: Exact working of JOIN in QlikView script?

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
Honored Contributor III

Re: Exact working of JOIN in QlikView script?

It should return same no. of record in brackets.

datanibbler
Esteemed Contributor

Re: Exact working of JOIN in QlikView script?

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?

MVP
MVP

Re: Exact working of JOIN in QlikView script?

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
Esteemed Contributor

Re: Exact working of JOIN in QlikView script?

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...
Not applicable

Re: Exact working of JOIN in QlikView script?

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

MVP
MVP

Re: Exact working of JOIN in QlikView script?

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
Esteemed Contributor

Re: Exact working of JOIN in QlikView script?

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
Contributor III

Re: Exact working of JOIN in QlikView script?

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

Community Browser