topic Re: Re: Re: Incorrect Total in Pivot Table in QlikView

Incorrect Total in Pivot Table

Anonymous — Fri, 02 May 2014 22:11:16 GMT

Hello! Pls. need your help now as I've spent for hours solving this issue.

My total in pivort table and text box is incorrect...some were duplicated (I'm expecting a total value of 26K)...I also tried using the aggr function but the value does not change. Is there something wrong with my script?

Thanks.

Re: Incorrect Total in Pivot Table

swuehl — Fri, 02 May 2014 23:22:36 GMT

Hard to say what is right or what is wrong without knowing what you want to model.

There are some records which seems to be only different in PostDate and SONumber.

If your facts show for example some movement of Material from one place to another, joing the Material price to each record will sum up to a too high material value (since the same material is counted multiple times).

But without knowing what you want to model and analyze etc. it's kind of guessing around.

Re: Incorrect Total in Pivot Table

Anonymous — Fri, 02 May 2014 23:31:57 GMT

hi! Swuehl,

I'm trying to get the total value per row....Since I don't have the std price for my table, I joined another table where std price is available,,,,Basically , My value should be Quantity * Std price...

Sorry if I didn't explain it clearly...I'm still new with Qlikview.

Tks.

Re: Incorrect Total in Pivot Table

Clever_Anjos — Sat, 03 May 2014 00:21:42 GMT

Try to not join your tables, how your data model looks like?

Re: Incorrect Total in Pivot Table

rajat2392 — Sat, 03 May 2014 04:28:57 GMT

Hi Cacosta,

Reload and check the attached file if it works...

Re: Incorrect Total in Pivot Table

Anonymous — Mon, 05 May 2014 23:57:55 GMT

Rajat,

Sorry! It didn't work.

Rgds

Re: Incorrect Total in Pivot Table

Anonymous — Mon, 05 May 2014 23:59:46 GMT

Hi! Clever,

If I don't join them, How can I get the value?...The price is on a separate table...

Tks.

Re: Incorrect Total in Pivot Table

SergeyMak — Tue, 06 May 2014 00:59:16 GMT

Hi cacosta35,

Please share you source qvd files.

It looks like you don't have to join some tables, but I can't tell which without source tables.

Re: Incorrect Total in Pivot Table

kaushiknsolanki — Tue, 06 May 2014 04:55:54 GMT

HI,

The reason for total not correct is your data gets duplicated when you are joining them.

Make sure that the table which you are joining (Table after Left Join Statement) has the unique record for the field which you are using for joining.

If this is not the case then it will duplicate the records.

Regards,

Kaushik Solanki

Re: Re: Incorrect Total in Pivot Table

Anonymous — Tue, 06 May 2014 16:02:41 GMT

Hi! Sergey,

Pls. see attached qvds

Re: Re: Re: Incorrect Total in Pivot Table

SergeyMak — Wed, 07 May 2014 01:48:57 GMT

The problem is your preceding load

I moved it to the first load and now everything is ok.

PFA

Re: Re: Re: Incorrect Total in Pivot Table

Anonymous — Wed, 07 May 2014 18:31:55 GMT

Thank you so much, Sergey...You just saved my day...I've been stuck with this for days now...

Well, I'm just wondering why I have to move my first load as most of my script structure are somewhat the same without any problem....and can yopu enlighten me why we need to put distinct at the Material field?

Thank you!

Re: Incorrect Total in Pivot Table

Clever_Anjos — Wed, 07 May 2014 19:20:43 GMT

Totally apart table? Don´t they have a key between them?

Re: Re: Re: Incorrect Total in Pivot Table

SergeyMak — Thu, 08 May 2014 01:48:29 GMT

So I should say that I didn't understand why you did additional preceding load, because I didn't see any reason for this except optimized load from qvd, but you didn't have it because of data transformation in the first load from qvd.

But I should add that now I don't understand why your preceding load affect to your results... Because if I just

LOAD *;

LOAD text(Material) as Material,

Plant,

CustomerID,

CustomerName,

MaterialDescription as [Material Description],

PostDate,

SONumber,

MPN,

text(Vendor- left(Vendor,4)) as Vendor,

DONumber,

AgeDays ,

num(Quantity,'###,###,##0') as [Total Quantity],

text(Batch) as Batch,

CustPO,

CustMaterial

FROM

[Stage2_GhubAging.qvd]

(qvd);

The problem appears.. it's weird, but.. at the moment I have no comments.. I will try to sort it out.

As for Material, you need to use Distinct because you have a lot of materials with Null() in both fields of Ref_STDPrice.qvd, so you probably need to cut them by WHERE or at least Distinct, but it was not a cause of problem

Re: Re: Re: Incorrect Total in Pivot Table

SergeyMak — Thu, 08 May 2014 02:06:57 GMT

So. I see that in your Stage2_GhubAging.qvd you have duplicates

With Distinct there are only 3513 rows and without distinct 14470.

Probably you have some joins when you create Stage2_GhubAging.qvd.

And you can use your preceding load if you want. But just add DISTINCT and you will not have the problem

Re: Re: Re: Incorrect Total in Pivot Table

Anonymous — Thu, 08 May 2014 15:27:48 GMT

Thank you so much for taking time to explain this to me...I just learned something new today.

Rgds.