topic Re: Left Join causing incorrect numbers in QlikView

Left Join causing incorrect numbers

rido1421 — Wed, 08 Jul 2015 10:11:24 GMT

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,

Re: Left Join causing incorrect numbers

crusader_ — Wed, 08 Jul 2015 10:15:23 GMT

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

Re: Left Join causing incorrect numbers

alexandros17 — Wed, 08 Jul 2015 10:16:29 GMT

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.

Re: Left Join causing incorrect numbers

avinashelite — Wed, 08 Jul 2015 10:17:58 GMT

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

Re: Left Join causing incorrect numbers

rido1421 — Wed, 08 Jul 2015 10:18:21 GMT

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

Re: Left Join causing incorrect numbers

crusader_ — Wed, 08 Jul 2015 10:27:47 GMT

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

Re: Left Join causing incorrect numbers

avinashelite — Wed, 08 Jul 2015 11:03:50 GMT

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

Re: Left Join causing incorrect numbers

rido1421 — Wed, 08 Jul 2015 11:46:58 GMT

Thank You ! This worked Great, Much appreciated

Thanks all for your Contributions.