Multiple Similar Databases - Load/Join/Concatenate?

[seniorps]

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!

[Anonymous]

I would concatenate them.

Generally in load scripts I avoid joining tables if possible.

[seniorps]

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?

[Anonymous]

Are your 4 loads from your 4 source databases loading the same fields ?

[seniorps]

Yes

[Anonymous]

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.

[seniorps]

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?

[Anonymous]

Hard to say without knowing your data & needs.

This blog may be helpful.

https://qlikviewcookbook.com/2009/11/understanding-join-and-concatenate/