topic Need to create pivot table from backend in App Development

Need to create pivot table from backend

qliksense1thomascook — Mon, 03 Mar 2025 12:03:31 GMT

Hello everyone,

I am trying to create a pivot table from the backend and store it in a csv file. And I have successfully created the pivot table but the order is missing when the data is pivoted. Request your help in sorting the order. Whether it be by using index or any other method. Attached a sample data, output and the used script below for reference.

The output should have the ageing field in the order of 0-30 Days, 31-60 Days, 61-90 Days, 91-180 Days, 181-365 Days, 1-3 Yrs, >3 Yrs

Thanks in advance.

Final:

Load Distinct Vendor

FROM

[lib://Path/Raw File for testing.qvd](qvd)

;

Generic:

Generic Load

Distinct Vendor,

Ageing,

sum([Amount in local cur.]) as Amount

FROM

[lib://Path/Raw File for testing.qvd](qvd)

Group By Vendor,Ageing

;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Generic.*') THEN

LEFT JOIN (Final) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

STORE Final into [lib://Path/Raw File OP test.csv](txt);

Re: Need to create pivot table from backend

morgankejerhag — Mon, 03 Mar 2025 14:06:18 GMT

It depends on the order that you join stuff. If you categories are fixed you can make a bit shorter code, but if you want to handle them dynamically, you could do something like:

Final:

Load Distinct Vendor

FROM [lib://Morgan Qlik Community:DataFiles/Raw File for testing.xlsx]

(ooxml, embedded labels, table is Sheet1);

Generic:

Generic Load Distinct

Vendor,

Ageing,

sum([Amount in local cur.]) as Amount

FROM [lib://Morgan Qlik Community:DataFiles/Raw File for testing.xlsx] (ooxml, embedded labels, table is Sheet1)

Group By Vendor, Ageing;

TablesTmp:

Load 1 as TableName AutoGenerate(0);

FOR i = NoOfTables()-1 to 0 STEP -1

Concatenate (TablesTmp)

Load

TableName($(i)) as TableName,

subfield(TableName($(i)),'.',2) as TableNameShort

AutoGenerate(1);

TablesTmp2:

Load

TableName,

if(right(TableNameShort,3)='Yrs',

if(left(TableNameShort,1)='>',

keepchar(TableNameShort,'0123456789')*365,

subfield(TableNameShort,' ',1)*365

subfield(TableNameShort,'-',1)

) as Days

resident TablesTmp where left(TableName,8)='Generic.';

drop table TablesTmp;

Tables:

NoConcatenate

Load * resident TablesTmp2 order by Days asc;

drop table TablesTmp2;

FOR i = 0 to NoOfRows('Tables')-1

Let vTable = peek('TableName',$(i),'Tables');

Trace table: $(vTable);

LEFT JOIN (Final) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

drop table Tables;

STORE Final into [lib://Morgan Qlik Community:DataFiles/Raw File OP test.csv](txt);

Re: Need to create pivot table from backend

qliksense1thomascook — Tue, 04 Mar 2025 04:48:14 GMT

Dear Morgankejerhag,

This solved my issue. Thank you very much.