3 Replies Latest reply: Mar 6, 2017 4:50 AM by Muthukumar Pandiyan RSS

    3 table association issue

    Jason Chong

      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??

       

       



        • Re: 3 table association issue
          Andres Prado

          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

          • Re: 3 table association issue
            Jonathan Dienst

            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.

            • Re: 3 table association issue
              Muthukumar Pandiyan

              Hi,

               

              When you load the three tables,

               

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