topic Re: Convert Rows to Columns in Load Script in QlikView

Convert Rows to Columns in Load Script

Anonymous — Thu, 31 Oct 2013 20:30:16 GMT

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

Re: Convert Rows to Columns in Load Script

rwunderlich — Fri, 01 Nov 2013 00:13:31 GMT

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

Re: Convert Rows to Columns in Load Script

Fri, 01 Nov 2013 05:06:53 GMT

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

Re: Convert Rows to Columns in Load Script

Gysbert_Wassenaar — Fri, 01 Nov 2013 07:26:30 GMT

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.

Re: Convert Rows to Columns in Load Script

Anonymous — Fri, 01 Nov 2013 09:18:28 GMT

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

Re: Convert Rows to Columns in Load Script

rwunderlich — Fri, 01 Nov 2013 18:21:26 GMT

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

Re: Re: Convert Rows to Columns in Load Script

Anonymous — Mon, 04 Nov 2013 10:37:53 GMT

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