Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I have a Fact Table and an Items Table
Below is what I am doing.
Items:
Load
ItemKey,
Item,
ItemDescription,
SerialNo,
ProductLevel1
ProductLevel2
From Items.qvd
Fact:
Load
ItemKey
SalesAmount,
SalesWeight,
DivisionNumber
From Fact.qvd
When Loading it like this for my Division Number "2000" I get a Sum(SalesAmount) of 7000
But when Doing the left join below I get 7400, I noticed the reason is because certain Items in Division Number "2000" is Doubled
what could be the reason?
Items:
Load
ItemKey,
Item,
ItemDescription,
SerialNo,
ProductLevel1
ProductLevel2
From Items.qvd
Fact:
Load
ItemKey
SalesAmount,
SalesWeight,
DivisionNumber
From Fact.qvd
Left Join(Fact)
Load
ItemKey
ProductLevel1
ProductLevel2
Resident Items;
The reason Im doing this left join is because I need the Product levels in the fact table for another use
Regards,
load distinct will not help in this case ..
Instead of joining try like this:
Map_ProductLevel1:
Mapping Load
ItemKey,
ProductLevel1
Resident Items;
Map_ProductLevel2:
Mapping Load
ItemKey,
ProductLevel2
Resident Items;
Fact:
Load
ItemKey
applymap('Map_ProductLevel1',ItemKey,null() as ProductLevel1,
applymap('Map_ProductLevel2',ItemKey,null() as ProductLevel2,
SalesAmount,
SalesWeight,
DivisionNumber
From Fact.qvd
Hello,
You answered your question by yourself
You're joining by ItemKey field, I believe that some ItemKeys are doubled with different ProductLevel1 and ProductLevel2.
Check it in the tableBox.
Hope this helps you.
Regards,
Andrei
Because the relationship of the 2 tables is not 1:1 but 1:n so for some records from the first table there exist more than one record in the second.
The left join multiply the rows of the first table for the rows of the second.
Hi Rido,
Its a valid case , since Division has to columns or values it treating it as two.
Instead of left join , try with the Appplymap function to get the level that will not duplicate the data
That is the issue but I tried doing a load distinct that doesnt help... what else could I try?
As it was mentioned by Avinashelite below, use ApplyMap instead of joining.
Also read article Don't join - use Applymap instead
Hope this helps you.
Regards,
Andrei
load distinct will not help in this case ..
Instead of joining try like this:
Map_ProductLevel1:
Mapping Load
ItemKey,
ProductLevel1
Resident Items;
Map_ProductLevel2:
Mapping Load
ItemKey,
ProductLevel2
Resident Items;
Fact:
Load
ItemKey
applymap('Map_ProductLevel1',ItemKey,null() as ProductLevel1,
applymap('Map_ProductLevel2',ItemKey,null() as ProductLevel2,
SalesAmount,
SalesWeight,
DivisionNumber
From Fact.qvd
Thank You ! This worked Great, Much appreciated
Thanks all for your Contributions.