Generic Load in Qliksense to transpose rows into columns

SunainaUmberkar — Wed, 14 Feb 2024 09:16:45 GMT

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)

Re: Generic Load in Qliksense to transpose rows into columns

SunainaUmberkar — Wed, 14 Feb 2024 11:34:48 GMT

Hello All,

Any help would be appreciated.

Thanks in advance.

topic Generic Load in Qliksense to transpose rows into columns in App Development

Generic Load in Qliksense to transpose rows into columns

Re: Generic Load in Qliksense to transpose rows into columns