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