Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am loading data from 4 similar databases, each representing a different company. I have added a Company ID to an empty field in the LOAD script.
The problem lies with joining tables in each database.
The LOAD script runs as follows:
CONNECT Database 1
LOAD Table 1 Fields
SQL SELECT Table 1 Fields
JOIN LOAD Table 2 Fields
SQL SELECT Table 2 Fields
LOAD Table 3 Fields
SQL SELECT Table 3 Fields
LEFT JOIN LOAD Table 4 Fields
SQL SELECT Table 4 Fields
This works fine and the data is how I expected. So, I copied the LOAD script and added a similar one for the next database:
CONNECT Database 2
LOAD Table 1 Fields
SQL SELECT Table 1 Fields
etc.
This brings multiple synthetic keys and the data is messed up.
I've also tried connecting to Database 1, loading Table 1, then connecting to Database 2, concatenating Table 1 etc. but I end up with similar problems.
I guess what I need to do is concatenate the tables first and then make the joins later? Is this possible? Or I am barking up the wrong tree?
Thanks for your help!
I would concatenate them.
Generally in load scripts I avoid joining tables if possible.
Hi Bill. I relatively new to Qlik and I don't understand your answer. The joins are necessary for the integrity of the data. I am interested in knowing whether it is possible to concatenate the data first and then add the joins later in the process?
Are your 4 loads from your 4 source databases loading the same fields ?
Yes
Then concatenating them all together would be the Qlik way to do it.
I came from a relational database / online transactional processing background and initially found abandoning joins and embracing concatenations unnerving, but now I am convert.
You say "The joins are necessary for the integrity of the data" could you clarify your concerns.
I have a feeling these concerns may well be irrelevant when you are using the Qlik QIX Associative engine as opposed to a Relational engine.
You could just take a leap of faith, concatenate all 4 tables together and hopefully be pleasantly surprised when it all works perfectly. You'll probably also find your load script runs faster and your dashboard responds quicker.
If I remove the joins and just load the data I get several synthetic keys and loops. The data itself is therefore often repeated in the visualisations. The data in the 4 source tables (there are 4 per company) is joined within each company in order to stop this from happening.
If I concatenate load the data I get no loops and synthetic keys, but none of the fields are linked in the way visualisations expect, so for example filters etc do not work.
Any other ideas?
Hard to say without knowing your data & needs.
This blog may be helpful.
https://qlikviewcookbook.com/2009/11/understanding-join-and-concatenate/