Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Incorrect Total in Pivot Table


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.

15 Replies
SergeyMak
Partner Ambassador
Partner Ambassador

The problem is your preceding load

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

PFA

Regards,
Sergey
Anonymous
Not applicable
Author

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!

Clever_Anjos
Employee
Employee

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

SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
Anonymous
Not applicable
Author

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

Rgds.