Solved: Reverse Pivot in Load - Qlik Community

pgdavis2 · ‎2010-10-04

<body>I'm trying to load data that is in numerous columns into a data structure with fewer columns but more records. This is basically the opposite of the "Generic Load" technique I've seen. I want to use the load script to transform something like this in the database: <a href="https://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0383.reverse_5F00_pivot.jpg"><img border="0" src="/resized-image.ashx/__size/550x0/__key/CommunityServer.Discussions.Components.Files/11/0383.reverse_5F00_pivot.jpg" /></a> For use in Qlikview. <col width="64"></col><col width="110"></col><col width="64" span="3"></col> Anyone have a script example for this? Thanks!</body>

pover · ‎2010-10-04

Try

Tmp_Table:
Crosstable (Hour,Value,2)
Load * from ex1.csv;

Table:
Load Name,
timestamp(timestamp#( Date & ' ' & Hour,'DD/MM/YYYY hh_mm' )) as Date_Time,
Value
Resident Tmp_Table;

Drop table Tmp_Table;

Regards.

View solution in original post

pover · ‎2010-10-04

Try

Tmp_Table:
Crosstable (Hour,Value,2)
Load * from ex1.csv;

Table:
Load Name,
timestamp(timestamp#( Date & ' ' & Hour,'DD/MM/YYYY hh_mm' )) as Date_Time,
Value
Resident Tmp_Table;

Drop table Tmp_Table;

Regards.

johnw · ‎2010-10-04

It's a crosstable load. If I understood what you want, you start with this:

MyTable:
CROSSTABLE (Hour,Value,2) // skip two fields (Name and Date)
LOAD *
...

Which gives you this:

Name, Date, Hour, Value
Joe, 1/1/2010, Hour1, 10
Joe, 1/1/2010, Hour2, 20
Joe, 1/1/2010, Hour3, 30

You should then be able to add the Hour to the Date to get the Date_Time:

LEFT JOIN (MyTable)
LOAD *
,timestamp(Date + mid(Hour,5)/24,'M/D/YY h:mm TT') as Date_Time
RESIDENT MyTable;

And drop what is no longer necessary to get to your final result:

DROP FIELDS Date, Hour;

Edit: Karl beat me to it.

pgdavis2 · ‎2010-10-04

Thank you both! This works very nicely