5 Replies Latest reply: Oct 31, 2013 3:38 PM by Bill Markham RSS

    Convert Columns to Rows in load script

    Bill Markham

      Hi

       

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

       

      This loads sample data into SourceTable.  In reality there will be a few dozen Flag fields and a few million ID's.

      SourceTable:

      LOAD * INLINE [

          ID, FlagA, FlagB, FlagC

          1, Yes, Yes, Yes

          2, Yes, Yes, No

          3, Yes, No, Yes

          4, No, Yes, No

          5, No, No, Yes

          6, No, No, No

      ];

       

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

       

      WantedTable :

      LOAD * INLINE [

          ID, FlagName, Value

          1, FlagA, Yes

          1, FlagB, Yes

          1, FlagC, Yes

          2, FlagA, Yes

          2, FlagB, Yes

          2, FlagC, No

          3, FlagA, Yes

          3, FlagB, No

          3, FlagC, Yes

          4, FlagA, No

          4, FlagB, Yes

          4, FlagC, No

          5, FlagA, No

          5, FlagB, No

          5, FlagC, Yes

          6, FlagA, No

          6, FlagB, No

          6, FlagC, No

      ];

       

      I have gone snowblind bananas trying to work out  how to do this, any suggestions would be most welcome.

       

       

      Best Regards,     Bill

        • Re: Convert Columns to Rows in load script
          Clever Anjos
          For i = 2 to NoOfFields('SourceTable')
            let field = FieldName(i,'SourceTable');
            Final:
            LOAD
            ID,
            '$(field)' as FlagName,
            $(field) as Value
            resident SourceTable;
          next
          
            • Re: Re: Convert Columns to Rows in load script
              Bill Markham

              Anjos

               

              Many thanks that has cracked it !!

               

              ....except that it produce a separate table for each Flag.

               

              So I have added the concatenate and pre-created the table with a Dummy field.

               

               

              Final:

              LOAD * INLINE [

                  Dummy

                  Dummy

              ];

               

              For i = 2 to NoOfFields('SourceTable')

                let field = FieldName(i,'SourceTable');

                Concatenate (  Final  )

                LOAD

                ID,

                '$(field)' as FlagName,

                $(field) as Value

                resident SourceTable;

              next

               

              drop field Dummy;

               

              This works perfectly now, but it seems a little inelegant to have to pre-create the dummy table.

               

              Any suggestions for elegance ?

               

               

              Best Regards,     Bill

            • Re: Convert Columns to Rows in load script
              sasi k

              Hi Bill

              Here we have to use CrossTable to convert l

               

              CrossTable(Flagname,value,1)

              LOAD * INLINE [

                  ID, FlagA, FlagB, FlagC

                  1, Yes, Yes, Yes

                  2, Yes, Yes, No

                  3, Yes, No, Yes

                  4, No, Yes, No

                  5, No, No, Yes

                  6, No, No, No

              ];



              You will get u r desired result

               

              Regards

              sasi

                • Re: Re: Convert Columns to Rows in load script
                  Bill Markham

                  Sasi

                   

                  Your suggestion also cracks it.

                   

                  I change it to test it from resident and it handles that fine.

                   

                  SourceTable:

                  LOAD * INLINE [

                      ID, FlagA, FlagB, FlagC

                      1, Yes, Yes, Yes

                      2, Yes, Yes, No

                      3, Yes, No, Yes

                      4, No, Yes, No

                      5, No, No, Yes

                      6, No, No, No

                  ];

                   

                  OutputTable:

                  CrossTable(Flagname,value,1)

                  LOAD *

                  resident SourceTable

                  ;

                   

                  And is more elegant.

                   

                  I'll test both suggestions tomorrow with loads of data..............

                   

                   

                  Best Regards,     Bill