Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Convert Rows to Columns in Load Script

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

1 Solution

Accepted Solutions

Re: Convert Rows to Columns in Load Script

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.


talk is cheap, supply exceeds demand
6 Replies

Re: Convert Rows to Columns in Load Script

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

Not applicable

Re: Convert Rows to Columns in Load Script

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

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.


talk is cheap, supply exceeds demand

Re: Convert Rows to Columns in Load Script

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

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

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

Community Browser