Convert Rows to Columns in Load Script

[Anonymous]

Hi

Having received a couple of excellent solutions re Converting Columns to Rows in the Load Script, this it the opposite as in 

Converting Rows to Columns in the Load Script

I have data in a resident table and below is a some sample data, plus the bb3.qvw attached has the sample data in it

This loads sample data into SourceTable.  In reality there will be a few million ID's, each with TimeIn and TimeOut pairs

SourceData:

LOAD * INLINE [

    ID, TimeIn, TimeOut

    1, 01:01, 02:02

    1, 03:03, 04:04

    1, 05:05, 06:06

    1, 07:07, 08:08

    1, 22:22, 23:23

    2, 09:09, 10:10

    2, 11:11, 12:12

    3, 13:13, 14:14

];

This Inline Load shows what I wish this sample data to be transformed into:

DesiredData:

LOAD * INLINE [

    ID, InA, OutA, InB, OutB, InC, OutC, InD, OutD, ExtraDataIgnored

    1, 01:01, 02:02, 03:03, 04:04, 05:05, 06:06, 07:07, 08:08, Yes

    2, 09:09, 10:10, 11:11, 12:12

    3, 13:13, 14:14

];

In theory each ID should not have more than 4 TimeIn and TimeOut pairs, but I am sure you know that in reality data is often not as reliable as one would hope for, hence the flag at the end for ExtraDataIgnored.

Unfortunately the source data rows could be in any random order.

Best Regards,     Bill

[rwunderlich]

Something like this:

Temp:

Generic LOAD

  ID,

  'In' & pick(AutoNumber(RecNo(),ID),'A','B','C','D','E','F','G','H','I'),

  TimeIn

Resident SourceData

;

Temp:

Generic LOAD

  ID,

  'Out' & pick(AutoNumber(RecNo(),ID),'A','B','C','D','E','F','G','H','I'),

  TimeOut

Resident SourceData

;

You can optionally learn how to put all those temp tables back into a single table here

http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html

-Rob

[#3]

Hi Bill,

You'll need to use the generic keyword which leaves you with multiple tables so you can find a nice solution to do multiple joins that combines all the tables into one table in this post. http://community.qlik.com/message/407192#407192

Karl

[Gysbert_Wassenaar · Accepted solution]

Result:
load
  ID
  ,SubField(TimeLine,'|',1) as InA
  ,SubField(TimeLine,'|',2) as OutA
  ,SubField(TimeLine,'|',3) as InB
  ,SubField(TimeLine,'|',4) as OutB
  ,SubField(TimeLine,'|',5) as InC
  ,SubField(TimeLine,'|',6) as OutC
  ,SubField(TimeLine,'|',7) as InD
  ,SubField(TimeLine,'|',8) as OutD
  ,if(len(SubField(TimeLine,'|',9)),'Yes') as ExtraDataIgnored;
load ID ,concat(TimeIn & '|' & TimeOut, '|',TimeIn) as TimeLine
Resident SourceData
Group by ID;

If TimeIn isn't a numeric field then use the time# function to make a time value of it first so it can be used in the concat function as sort field.

[Anonymous]

Rob, Karl & G

Many thanks for your suggestions, they all look promising.  I'll check them out against a few millions rows later today.

Best Regards,     Bill

[rwunderlich]

I of course like my solution but I'll warn you that the AutoNumber with many different Ids will run very slow so may not be your best option. If so, you can pre-build that sequence # with a peek() or use Gysbert's idea.

-Rob

[Anonymous]

Gysbert

Your solution works perfectly, it takes about 1 minute to process approx. 8 million rows which for my needs is more than adequate.

[Rob did warn that that the AutoNumber in his suggestion with many different Ids will run very slow and was indeed correct]

Many Thanks,    Bill