topic Incorrect Values - Double amounts due to Duplicate Values in Joining key in App Development

Incorrect Values - Double amounts due to Duplicate Values in Joining key

AjjuSid — Wed, 26 Jun 2024 02:08:13 GMT

Hi Experts,

I have fact and dimension tables, I am trying to find the sum of a measure from my fact Table, but the result is incorrect due to duplicate values In my key (Dimension Table). For Example, for some duplicate key combinations, it is summing the values twice hence I am getting inflated values. To overcome this I have tried to use
1)distinct Keyword in the load script before joining dim Table
2)Sum(Distinct Amount), Sum - Aggr Combinations in the front end.

Unfortunately, I am still getting inflated values, Please let me know of any viable solution to overcome this.

Re: Incorrect Values - Double amounts due to Duplicate Values in Joining key

anat — Wed, 26 Jun 2024 02:26:24 GMT

Distinct will work only if entire record is duplicated,then you will get one entry

Can you post some sample data

Re: Incorrect Values - Double amounts due to Duplicate Values in Joining key

AjjuSid — Wed, 26 Jun 2024 02:49:30 GMT

I got it, Thanks for the clarification.

Please find the sample dummy data.

Table 1

AName BName Amount

Abcde kellog $100

Table 2

AName BName Category

Abcde Kellog AB

Abcde Kellog CD

Resulting Table

AName BName Category amount

Abcde Kellog AB $100

Abcde Kellog CD $100

This is sample data, Please let me know if this works

Re: Incorrect Values - Double amounts due to Duplicate Values in Joining key

marksouzacosta — Wed, 26 Jun 2024 04:13:38 GMT

Hi @AjjuSid,

To make your life easier, I do not recommend joining the tables in this case. This is what is causing your problems. It is fine to keep them separated. However, you still have a problem: the Synthetic Key. To solve that, you can create a composed key. Example:

Fact:
LOAD 
    Hash128(AName,BName) AS %DimensionKey,
    Amount
INLINE [
AName, BName, Amount
Abcde, Kellog, $100
];
 
Dimension:
LOAD 
    Hash128(AName,BName) AS %DimensionKey,
    AName,
    BName,
    Category
INLINE [
AName, BName, Category
Abcde, Kellog, AB
Abcde, Kellog, CD
];

Autonumber '%*';

Your data model will look like this:

Note: I have added Hash128 and the AutoNumber statement at the end of the code just to show a better way to create composed keys in Qlik Sense. You can achieve the same result in many different ways.

Re: Incorrect Values - Double amounts due to Duplicate Values in Joining key

AjjuSid — Fri, 28 Jun 2024 04:32:36 GMT

Thanks a lot for the help! It works for me