Convert columns into rows

[Shani1]

Hi 

I have a long data file with months name as columns and want to reshape the table in load script. tried crosstable but there was no success

here is example data what i have

Serial	Min_2020_01	Min_2020_02	Min_2020_03	Mean_2020_01	Mean_2020_02	Mean_2020_03	Max_2020_01	Max_2020_02	Max_2020_03
12345	1	1	1	1.5	1.5	1.5	3	3	3

and i want to convert this to this

Serial	Date	Min	Mean	Max
12345	2020-Jan	1	1.5	3
12345	2020-Feb	1	1.5	3
12345	2020-Mar	1	1.5	3

How do i do that. 

[Anil_Babu_Samineni]

One way perhaps this way?

[rwunderlich · Accepted solution]

How about:

TempData:
CrossTable(Stat_Month, Value)
LOAD *
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Convert-columns-into-rows/td-p/1760422]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

MasterData:
LOAD
  Serial & '-' & Date as Key,
  *
;
LOAD
  Serial,
  Date(MakeDate(SubField(Stat_Month,'_',2), SubField(Stat_Month,'_',3)), 'YYYY-MMM') as Date,
  SubField(Stat_Month,'_',1) as Stat,
  Value
Resident TempData
;
Drop Table TempData;

Stats:
Generic LOAD
  Key,
  Stat,
  Value
Resident MasterData
;
DROP Fields Value, Stat;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Shani1]

Many Thanks, This is working as it supposed to be. 

It create 4 tables, is it possible to make a single table 

[MayilVahanan]

HI @Shani1 

You can use HIC solution for this 
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

In addition to Rob solution, just added HIC solution for your reference. 
Ex:

TempData:
CrossTable(Stat_Month, Value)
LOAD *
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Convert-columns-into-rows/td-p/1760422]
(html, codepage is 1252, embedded labels, table is @1);

MasterData:
LOAD
Serial & '-' & Date as Key,
*
;
LOAD
Serial,
Date(MakeDate(SubField(Stat_Month,'_',2), SubField(Stat_Month,'_',3)), 'YYYY-MMM') as Date,
SubField(Stat_Month,'_',1) as Stat,
Value
Resident TempData
;
Drop Table TempData;

Stats:
Generic LOAD
Key,
Stat,
Value
Resident MasterData
;
DROP Fields Value, Stat;
Let vNoOfTables = NoOfTables();

For cnt=0 to $(vNoOfTables)-1

Let vTableName=TableName($(cnt));

TableList:

LOAD

'$(vTableName)' as TableName

Autogenerate(1);

Next

Let vFirstTableName=Peek('TableName',0,'TableList');

Let vNumberOfRows = NoOfRows('TableList')-1;

For cnt=1 to $(vNumberOfRows)

Let vTableName=Peek('TableName',$(cnt),'TableList');

Join($(vFirstTableName))

LOAD Distinct * Resident $(vTableName);

DROP Table $(vTableName);

Next

Rename Table $(vFirstTableName) to MainTable;

[Shani1 · Accepted solution]

Exactly what i was looking for. many thanks to both of you