Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

3 table association issue

I have 3 table

Table A:

Date            fac          limit

Nov-16     F00001      $10

Nov-16     F00002      $20

Table B:

Date           fac            col.id

Nov-16     F00001      C001

Nov-16     F00002      C002

Table C:

Date           col         value

Nov-16      C001        $5

Nov-16      C002        $6

I upload 3 table to qlik, it automatically associate 3 table.Then I create a straight/pivot table and it show:

fac                  col          sum(limit)      sum(value)

F00001       C001             $10                $5

F00001        C002             $10                $6

F00002       C001             $20                $5

F00002        C002             $20                $6



However, what i expect should be something like this (since F00001 is link to C001 only in table B):

fac                  col          sum(limit)      sum(value)

F00001       C001             $10                $5

F00002       C001             $20                $5

I have checked with community and it seems it is related to Synthetic Key issue.

If I delete Table C, it can correctly link the fac to col.

Anyway to handle this kind of issue??



3 Replies
andrespa
Not applicable

Re: 3 table association issue

Hi, the issue is that Qlik Sense always try to associate all the fields with the same name. So in your case, it is associating Date and col between table 1 and table 2, and then is associating fac and col between table 2 and table 3. You need to build a unique key for all the tables or a unique key for each of the particular associations (i.e. Date&col as key_1 and fac&col as key_2) Obviously this will solve your issue but you need to add maybe another logical field in order to get what you want.

Hope it helps.

Andrés

jontydkpi
Not applicable

Re: 3 table association issue

Unless the col.id fieldname is a typo, your key is incomplete

When you load Table B, alias col.id to col to complete the key.

     [Table B]:

     LOAD col.id as col,

          ...

Then the tables should associate correctly.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
muthukumar77
Not applicable

Re: 3 table association issue

Hi,

When you load the three tables,

In Table 2, Put col.id ascol like this.

Muthukumar Pandiyan