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

[AjjuSid]

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. 

[anat]

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

Can you post some sample data

[AjjuSid]

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

[marksouzacosta · Accepted solution]

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.

[AjjuSid]

Thanks a lot for the help! It works for me