Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 here is a good link explaining the join, keep and concatenate options you have:
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)
It should return same no. of record in brackets.
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?
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
Hi swuehl,
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
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
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
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