Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
the synthetic key should create all the options from the two tables
here is some reference to synthetic keys
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
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).
Removing the vPriorYearEnd from the set analysis doesn't help either. I tries with just Sum(Daily_Target), and it still cuts off at august, even though the data in the QVD exists up until the end of December.