Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Exactly what i was looking for. many thanks to both of you
One way perhaps this way?
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
Many Thanks, This is working as it supposed to be.
It create 4 tables, is it possible to make a single table
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;
Exactly what i was looking for. many thanks to both of you