Need to create pivot table from backend

[qliksense1thomascook]

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);

[morgankejerhag · Accepted solution]

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);

next

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)];

NEXT

drop table Tables;

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

[qliksense1thomascook]

Dear Morgankejerhag,

This solved my issue. Thank you very much.