6 Replies Latest reply: Nov 4, 2013 5:37 AM by Bill Markham RSS

    Convert Rows to Columns in Load Script

    Bill Markham

      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