9 Replies Latest reply: Apr 18, 2016 8:18 AM by Siddharth Sheshadri RSS

    Creating custom associations

    Siddharth Sheshadri

      Hello,

       

      I am new to Qlik Sense.

      Could anyone guide me as to how I can create my own association instead of using Qlik's analyzing engine?

       

      I cannot figure out a way to create my own association.

       

      Thanks,

      SS.

        • Re: Creating custom associations
          Marcus Sommer

          Associations will be made with the fieldnames. That meant you need to change the fieldnames if you want to create an association or to break one in the following manner:

           

          OldName as NewName

           

          Further helpful wille be: Get started with developing qlik datamodels.

           

          - Marcus

            • Re: Creating custom associations
              Siddharth Sheshadri

              Hi Marcus,

               

              That is what I figured and I did change the names accordingly.

              This is a snippet of the data model. See the last columns in each of the table named as "Date", although they are prefixed with the table name. Capture.PNG

               

              What I was trying to understand is, is there no way that I can choose my own associations rather than taking Qlik's associative engine's recommendations?

               

              P.S: I think I am intelligent enough to figure out the associations I need.

              Marcus Sommer <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

               

              Associations will be made with the fieldnames. That meant you need to change the fieldnames if you want to create an association or to break one in the following manner:

               

              OldName as NewName

               

              Further helpful wille be: Get started with developing qlik datamodels.

               

              - Marcus

                • Re: Creating custom associations
                  Siddharth Sheshadri

                  Figured it out. I just had to remove the Table name which was prefixed.

                   

                  Thanks Marcus. I marked your answer as correct answer.

                  I was trying to achieve the Billed table & the receipts table to be connected to my calendar master table so I can use the master Calender table as my dimension.

                   

                  This is what I got after changing the script from Data Editor. Does this look right to you?

                   

                  Testing on it.

                   

                  Capture.PNG

                    • Re: Creating custom associations
                      Marcus Sommer

                      I suggest to adjust the datamodel then you have now a synthetic key between your billed and receipts table because they have more then one field in common (Date and Bill ID). This musn't be technically wrong but it's best practice to resolve such automatically generated synthetic keys/tables with own solutions to ensure that you understand your datamodel and also knows the quality of your data: Synthetic Keys

                       

                      In your case this could be a composite key like:

                       

                      Date & '|' & [Bill ID] as BilledReceiptsKey

                       

                      But it could be that these composite key covered not each case (maybe some dates and/or bill id's are in one table and not within the other or reverse) and you need to transfer some fields from table to another (per joining or mapping) and/or fill up missing data/links and/or to create a link-table between them. Further I could imagine that these dates couldn't be simply associated because they are more a chain of events and you need to harmonize them per Canonical Date.

                       

                      I think you will need to dive deeper in your data to determine which data are related to eachother in which way - indepent from their origin fieldnames which might give an orientation but not more.

                       

                      - Marcus

                        • Re: Creating custom associations
                          Siddharth Sheshadri

                          I made a few more changes, this is what the key looks like.

                           

                          Can you help me figuring out what exactly this means? Does it mean it has created a composite key of all this?(I hope not). How can I test this?

                           

                          Besides, I cannot join the tables because if I do so, I cannot use these as a common dimension for both tables when I am filtering, Can I?


                          $Syn 1 = Date+Bill ID+Matter Name+Client Name+Client Group+Partner+City

                            • Re: Creating custom associations
                              Marcus Sommer

                              Yes it meant that qlik has generated a composite key from all those fields. How to solve? Probably you need to do several things, for example to divide fields like Client Name, Client Group, Partner City into dimension-tables, creating of composite keys between them and/or creating of a link-table.

                               

                              It won't be easy for as beginner but there is no way around then to learn these datamodel basics. For this I suggest like above mentioned to investigate your data carefully and to read the guidings within: Get started with developing qlik datamodels and the other links.

                               

                              Further helpful to start is to going forward step by step - this meant to load at first one table and then examining it, then comes the next table which must be merged or associated and again it will be examinated within the gui per listboxes and charts and then follow the next table ... This ensured that you keep on overview and could learn and avoids an unsystematically trial and error approach with rather accidental results.

                               

                              - Marcus