Foreign key, primary key association issue

chrisqlik — Sat, 16 Nov 2024 19:10:16 GMT

Hello all, apologies in advance if this is super elementary.

I have a fact table and dimension table associated by fk/pk. The key in this case is a combination of numeric codes that we use to distribute expenses to the correct business unit/site/department/expense type. The dimension table contains the individual segments of each combination. Something like this:

The issue: I created a table chart in Qlik Sense to view the data, and it does not appear to be associated properly. For rows in which I have a non-zero value in the 'DistributedAmount' field, the segment values are null. But in the rows where there is no DistributedAmount ($0, not null), I get the segment values. Something like this:

Notable info:

- The present keys in the fact table represent only ~5% of the keys present in the dimension table

- The keys that do have a non-zero DistributionAmount are the ones that appear in the fact table

- The primary key is a derived field that concatenates all the segments to form the code combination

- In the load script, where I load the dimension table data, I have attempted to limit what is brought in by using 'WHERE EXISTS (CodeCombination, SEGMENT1 & '-' & SEGMENT2 & '-' & SEGMENT3 & '-' & SEGMENT4 &'-'& SEGMENT5 &'-'& SEGMENT6)' -- which does eliminate the rows that contain code combinations that have zero DistributedAmount... however the corresponding segment values, for the code combinations that do have non-zero DistributedAmount, are still null.

Thank you. Your feedback is very much appreciated.

topic Foreign key, primary key association issue in App Development

Foreign key, primary key association issue