topic Re: Synthetic Key issue in App Development

Synthetic Key issue

Tue, 20 Dec 2016 08:38:39 GMT

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 — Tue, 20 Dec 2016 08:52:04 GMT

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

lironbaram — Tue, 20 Dec 2016 08:54:58 GMT

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

marcus_sommer — Tue, 20 Dec 2016 10:00:39 GMT

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

Re: Synthetic Key issue

Tue, 20 Dec 2016 10:08:32 GMT

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).

Re: Synthetic Key issue

Tue, 20 Dec 2016 10:16:58 GMT

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.