Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
The problem is your preceding load
I moved it to the first load and now everything is ok.
PFA
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!
Totally apart table? Don´t they have a key between them?
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
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
Thank you so much for taking time to explain this to me...I just learned something new today.
Rgds.