5 Replies Latest reply: Dec 20, 2016 5:16 AM by Daniel Balchasan RSS

    Synthetic Key issue

    Daniel Balchasan

      Hello,

       

      I have 2 tables which I load, which are joined on a synthetic key (CENTER_ID and DATE). One contains the visits of members, and the other contains the visit targets for each center. I use them to show the performance to target.

       

      I noticed that centers which had no visits on a certain day, did not show a target for that day as well. It seems like the synthetic key created by the platform, requires the combination of CENTER_ID and DATE to exist on both tables, otherwise it won't load the data.

       

      I don't think it's how its supposed to behave (Correct me if I'm wrong), and it seems inconsistent as well. Certain loads the data would load as I expect, but the next one it would not.

       

      I was able to work around this by creating a table that connects the other 2 using a concatenated key, instead of letting the system create a synthetic key automatically.

       

      My question is this:

      Is having every combination of CENTER_ID and DATE required on both tables, in order to used a system created synthetic key?

      Should I avoid synthetic keys in general?

        • Re: Synthetic Key issue
          Gysbert Wassenaar

          I very much doubt the synthetic key is the issue. But without looking at the data I can't really say. Can you post a small Qlik Sense app that shows the problem?

            • Re: Synthetic Key issue
              Liron Baram

              the synthetic key should create all the options from the two tables

              here is some reference to synthetic keys

              Synthetic Keys

              • Re: Synthetic Key issue
                Daniel Balchasan

                Thanks for your reply.

                 

                I think you're right, and it's not the synthetic key (My fix with the concatenated key didn't solve it after all).

                I'm still getting different results every few time I load the data, though the QVD's themselves don't change.

                I'm not allowed to send the actual data, and I haven't been able to reproduce this with a test data set.

                 

                I think the issue might be related to my Set analysis somehow. I use a variable (In my load script) to only load data from the last year, based on the selection:

                let vPriorYearEnd = '=MonthEnd(addmonths(YearStart(max(CHECK_IN_DATE)),-1))';

                 

                I then use this variable in my set analysis:

                Sum({<CHECK_IN_DATE={">$(vPriorYearEnd)"}>}Daily_Target)

                 

                It seems that the calculation of vPriorYearEnd isn't working properly sometimes. If I only load my TARGETS, the data is always fine, but it seems that sometimes the system checks the max(CHECK_IN_DATE), based on the CHECKINS table, and not from the whole data set.

                 

                My biggest issue seems to be that it's inconsistent. I attached 2 screenshots of the same visualization. Each time was after I did a load (The load script hasn't changed, and the data files haven't changed either).

                wrong.PNGcorrect.PNG

              • Re: Synthetic Key issue
                Marcus Sommer

                The synthetic key isn't your problem - whereby you should always avoid them even if they work identically to an own combined key. Your problem are missing key-data and you couldn't solve it with a table-association. One solution would be to concatenate both tables which is quite easy and performant and the other would be to create a link-table between both tables which contained all key-values. See for this: Concatenate vs Link Table

                 

                - Marcus