Hello All,
I have a scenario where i need to transpose my row data into columns. I have a data of Unit, a Bucket is created based on Days and USD is the measure. I need to convert this Bucket values from rows to columns. I have used Generic load in script. After generic load I have stored all the generic tables into a single qvd.
After storing, i need to load the qvd and perform sum on the bucket values. While doing sum there are duplicates hence Sum(bucket) is showing more value. When i use distinct in front end it shows correct value, but i need to be distinct from backend. Below is the script.
Step1:
AgePivot:
Generic Load UNIT as UNIT, Bucket, USD as Value;
LOAD
UNIT,
USD,
Bucket
FROM qvd;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
Next vTableNo
CombinedGenericTable:
Load distinct
UNIT,
USD,
Bucket
From qvd;
For each vTableName in $(vListOfTables)
If vTableName like 'AgePivot.*' Then
Left Join (CombinedGenericTable)
Load * Resident $(vTableName);
Drop Table $(vTableName);
Endif
Next vTableName;
Store * From CombinedGenericTable Into [WIP_AgePivot.qvd](qvd);
Step2:
WIP_AgePivot:
Load UNIT,
Sum ( "031060"+ "000030"+"061090"+"151180"+"091120"+"121150"+"181") as Aging_Amount
Group by VCUNIT
;
LOAD
UNIT,
Bucket,
USD,
"031060",
"000030",
"061090",
"151180",
"091120",
"121150",
"181"
FROM [WIP_AgePivot.qvd](qvd);
I need to calculate Aging_Amount in the backend and store in qvd, hence I am doing the Sum in script.
But Sum is generating duplicate values. Kindly help. I have attached the excel with data.
For UNIT-17118, if we check ideally sum of all buckets in the screenshot should be 66,856.4. But we are getting different value as shown in ss2 when we do Sum(Aging_Amount)
