Excel Tables linked through multiple dimensions

[danialier]

Hello,

I have a QV taking the information from five different excel files. All of them are linked through one dimension but there are two that are linked with two dimensions (month and country).

Is that an issue ? Which are the pros and cons of having two tables linked with multiple dimensions rather than a single dimension ?

thks!

dani

[MK_QSL]

There should not be any problem except they will create a syn1 table due to synthetic key.

You can either follow a concatenate or link table approach to remove the synthetic key..

Please find more on community about how to remove synthetic key

[#3]

If two tables or more are joined by more than one dimension - it will create a synthetic key. Do you get the same - a table with $Syn?

If that's the case, it is better to avoid it by concatenating the 2 dimension into one. This can be done in the load file or in the load process.

For example:

LOAD

A & B as C

...

[MK_QSL]

Check this really nice topic...

Re: Should We Stop Worrying and Love the Synthetic Key?

[Colin-Albert]

Can you give more details on what fields are in each table.

It could be that the data should be concatenated not joined.

A screen shot of the tables from the table viewer may help.

[PrashantSangle]

Hi,

As you said that all table should " linked through one dimension " then instead of concatenate you just rename the fields which you don't want to link.

Regards,

[#7]

Rather than having multiple tables(1 per sheet), try to JOIN or CONCATENATE them into fewer tables.

Your model will be simpler to read.

You will avoid synthetic keys  (even if, I am not sure, that you necessarily, need to remove them)

Fabrice

[danialier]

Here an attachment of the table viewer.

In the QV I have a Chart that takes data from the two tables linked through two dimensions, and this is why I need both dimensions in both tables unless there is another solution...

thks,dani

[#9]

Dani,

I would say:

1) either keep the synthetic key (if not big data)

2) or (like everybody tells us to do)

a) in the current tables, concatenate the two fields A & B as sth, or use the function AutoNumber(A&B) to get an integer. Remove both fields A & B

b) create a new table doing the link between A&B with A, and with B

therefore your new table will have 3 fields:

1st field: A&B (or AutoNumber)

2nd Field: A

3d field: B

Of course, in this new table, you must have ALL A&B values that are in both data tables. If not, some data will be missing.

Fabrice

[#10]

For big data, it is an issue. Because if there is Synthetic Table, it make qlikview jump to more table when it's do the associative.

For Example:

If there is only table A and B, when qlikview do the associative, there will be a link just table A and B.

But if you have Synthetic table, then it will do link from table A to Table Synthetic table and then table B.

It won't have big difference of performance if you have small data, but it will for big data.