topic Re: Data modeling and auto join(Synthetic keys) in QlikView

Data modeling and auto join(Synthetic keys)

Sat, 15 Nov 2014 15:03:13 GMT

Hi All,

I am facing same problem with data model. I have 4-5 de-normalized table. each are with 9-10 common different fields.

So what is the problem . Qlikview is taking auto join b/w them. So, my data is not coming properly.

it is creating multiple synthetic keys.

Please,Give me a solution.

ASAP.

Thanks.

Re: Data modeling and auto join(Synthetic keys)

Sat, 15 Nov 2014 16:36:33 GMT

if the field are same then qlikview takes autoconcatenation

to avoid this go for noconcatenation

ex:

load *

from table1;

noconcatenation

load *

from table2;

Re: Data modeling and auto join(Synthetic keys)

maxgro — Sat, 15 Nov 2014 16:44:57 GMT

qlik join by field name

if you rename the fields in the load, you can change/eliminate all the join

and start with a model with no join

then you have to add the association you want to your model

tab0:

load

field1, field2, field3, ...., field10,

field7 & '-' & field9 as fieldjoinnew

from ....;

tab1:

load

field1 as field1new, // remove association with tab0

field2 as field2new, // remove association with tab0

.......

field7 & '-' & field9 as fieldjoinnew // new association with tab0

from .....;

Re: Data modeling and auto join(Synthetic keys)

morgankejerhag — Sat, 15 Nov 2014 17:09:41 GMT

Hi,

You have two options on how to create the model:

1. Concatenate all the tables into one single table. Preferably add some column like RowType that differs between the lines. This can later be used in set analysis.

2. Either manually rename columns or use the qualify command. This will make field names unique. You probably want some keys to be unqualified.

Re: Data modeling and auto join(Synthetic keys)

Sun, 16 Nov 2014 05:02:51 GMT

But in this solution, will same filter work on both?

Like I have brand as common field in b/w 2 table.

And I have used no concatenation that time.

I want to filter by brand then it will work?

thanks.

Re: Data modeling and auto join(Synthetic keys)

Sun, 16 Nov 2014 05:08:14 GMT

Will link table work in my case?

If yes then can you give me any source to refer link table?

thanks.

Re: Data modeling and auto join(Synthetic keys)

maxgro — Sun, 16 Nov 2014 10:18:21 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

Qlik Tips: Update to Link Table rules

Link Table in QlikView | Learn QlikView

Re: Data modeling and auto join(Synthetic keys)

Sun, 16 Nov 2014 15:04:22 GMT

When designing a datamodel in Qlikview, circular reference is quite a common thing.

There are several solutions to prevent such loops like the concatenate function or link table. Although the best solution to fix circular reference depends on the situation, I personally prefer the link table most of the times. When you have two fact tables for example, which shares more then one dimension, it means there is circular reference.

To prevent this situation we could use the concatenate function to create one major facttable. You could consider this solution when both tables have many similarities. When using the concatenate function, always dwell on the fact that, if the measure fields in both facttables are the same, calculations will cover all records of the table. Of course there is the possibility to create a field and specify the facttype so you can select ‘Fact1’ or ‘Fact2’ and use set analysis in your expressions but I think it’s clear that this isn’t an easy solution when the records always need to be separated in all expressions.

Example 1: Concatenate two fact tables.

//**************************************************************************
// FACT TABLE
//**************************************************************************
FACT:
load
‘Fact1’        AS Facttype
,    ID            AS %KEY_FACT1_ID
,    Measure
From……

CONCATENATE

load
‘Fact2’        AS Facttype
,    ID            AS %KEY_FACT2_ID
,    Measure
From……

What about concatenate if the fields in the facttables are quite different? Rationally this does not seem to be a good idea is it? Let’s demonstrate this with an example of two different processes. The facts are:

- The purchase of products
- The selling of products

When loading the original datamodel, Qlikview has to create a synthetic key because the dimensions date and product are shared. Although the synthetic key in this situation works fine, it’s better to prevent this.

When we concatenate the purchase and selling table, the synthetic key is gone and the problem is solved. Allthough naturally this just doesn’t feel like the ‘right’ way, there is no difference for the end user because the interface of Qlikview works on attribute level. For Qlikview, this is best practice and a good way of solving circular reference. Keep in mind that, for solutions with many tables, the overall picture for the developer is getting less clear. In that case it’s better (in my opinion) to use link tables.

The use of Link Tables
With the use of link tables, it’s possible to keep the facttables separated from each other. The advantage of this solution is that there is no need for set analysis when doing calculations for fact 1 or fact 2 when they share the same measure. Another reason for choosing this method is to keep the datamodel a logical one.

The link table contains the key fields of the facts and dimensions. It is likely that the two fact tables don’t have the same keyfields. We can use both unique key fields and give them the same fieldname in the linktable to create a unique id for each record. Because Qlikview loaded all necessary keys in the facttables, we can simply use a resident load. Below shows an example of a linktable. As you can see, I use %% to indicate it’s a key for the dimension and % for the fact.

//**************************************************************************
// LINK TABLE
//**************************************************************************
LNK_TABLE:
load distinct
%KEY_FACT1_ID        AS %KEY_LNK_ID
,    %KEY_DIM1_ID        AS %%KEY_DIM1_ID
,    %KEY_DIM2_ID        AS %%KEY_DIM2_ID
RESIDENT Fct1;

CONCATENATE

load distinct
%KEY_FACT2_ID        AS %KEY_LNK_ID
,    %KEY_DIM1_ID        AS %%KEY_DIM_ID
,    %KEY_DIM2_ID        AS %%KEY_DIM2_ID
RESIDENT Fct2;

Automated Link script

In the share Qlikviews section of the QlikCommunity you can find a script from Lars Christensen that automatically creates a linktable and removes synthetic keys from “any” combination of tables. Using this code in our example brings the following model:

Bear in mind, use carefully, and don’t trust it is working 100% until you have verified your data - this is experimental code.

Re: Data modeling and auto join(Synthetic keys)

morgankejerhag — Sun, 16 Nov 2014 15:38:42 GMT

A link table would work too, but is probably challenging if you have ten key fields.

Are you sure it doesn't work to concatenate the data instead? That would be much easier if it works. Do you need to mix values from different tables in the same aggregation (sum/count/etc)?

Re: Data modeling and auto join(Synthetic keys)

Sun, 16 Nov 2014 16:28:19 GMT

If tables are linked properly then filters should work across the tables. And in your case it seems most of the fields are same, so I suggest you to concatenate. You are getting synthetic keys because of more than one column name is similar in two tables.

We would be in better position to help you by looking into your app.

Regards,

Navdeep

Re: Data modeling and auto join(Synthetic keys)

Mon, 17 Nov 2014 05:10:43 GMT

Yes. I need different values from several tables.

that's why concatenate may not work.