topic Re: Excel Tables linked through multiple dimensions in QlikView

Excel Tables linked through multiple dimensions

danialier — Fri, 14 Mar 2014 12:01:57 GMT

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

Re: Excel Tables linked through multiple dimensions

MK_QSL — Fri, 14 Mar 2014 12:10:31 GMT

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

Re: Excel Tables linked through multiple dimensions

Fri, 14 Mar 2014 12:10:58 GMT

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

...

Re: Excel Tables linked through multiple dimensions

MK_QSL — Fri, 14 Mar 2014 12:23:19 GMT

Check this really nice topic...

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

Re: Excel Tables linked through multiple dimensions

Colin-Albert — Fri, 14 Mar 2014 12:23:34 GMT

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.

Re: Excel Tables linked through multiple dimensions

PrashantSangle — Fri, 14 Mar 2014 12:31:07 GMT

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,

Re: Excel Tables linked through multiple dimensions

Fri, 14 Mar 2014 12:44:25 GMT

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

Re: Re: Excel Tables linked through multiple dimensions

danialier — Fri, 14 Mar 2014 13:14:42 GMT

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

Re: Excel Tables linked through multiple dimensions

Fri, 14 Mar 2014 14:37:29 GMT

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

Re: Excel Tables linked through multiple dimensions

Fri, 14 Mar 2014 14:51:15 GMT

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.

Re: Excel Tables linked through multiple dimensions

MK_QSL — Fri, 14 Mar 2014 14:59:02 GMT

Please find enclosed simple example showing how to remove synthetic key using LinkTable.

You can see table viewer by CTRL + T

You can use the same approach for removing synthetic key...