Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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.
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
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
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