Left Join causing incorrect numbers

[rido1421]

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,

[crusader_]

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

[alexandros17]

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.

[avinashelite]

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

[rido1421]

That is the issue but I tried doing a load distinct that doesnt help... what else could I try?

[crusader_]

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

[avinashelite · Accepted solution]

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

[rido1421]

Thank You ! This worked Great, Much appreciated

Thanks all for your Contributions.