2 Replies Latest reply: Jan 16, 2017 8:18 AM by Philipp Heinz RSS

    Easy way to transform tablecolumn to headerrow?

    Philipp Heinz

      Hi,

       

      i am trying to transform a table column to a headerrow. My data looks like this:

       

      ReportTypeRecordNo.FieldNo.Content
      107583001NR:11
      107583002VA:1
      107583003VA:5.79
      107583005NR:2
      107583007NR:101

       

      What i am trying to achieve is a format like this:

       

      ReportType
      RecordNo.30013002300330053007
      10758NR:11VA:1VA:5.79NR:2NR:101

       

      I already tried out the CrossTable() Function, but with no success.

      • Is this even possible with the crosstable function?
      • Is there some other function that can help me with this problem?

       

      Any help is really appreciated!

       

      Philipp

        • Re: Easy way to transform tablecolumn to headerrow?
          Liron Baram

          hi

          the function you need is Generic load

          which create saperate table for each value in FeildNo. field

          and add the values from the field Content

          attach is an example and below is a script

          your script should look like this

           

           

           

           

           

           

          Data:

          generic LOAD

              RecordNo.,

              FieldNo.,

              Content

          FROM

          [C:\Community\Book1.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

           

           

           

           

          let vTables= NoofTables(); // finding number of tables

           

           

          let vDataTableName = TableName(0);////finding the name of first table

           

           

          RENAME Table $(vDataTableName) to Data;//rename first table

           

           

          //////////////////////connecting all tables back to one table //////////////////////////

          ////////////////creating a table with all table names //////////////////////////////////////////

          for i =1 to NoofTables()-1

          Tables:

          load TableName($(i)) as TableName ,$(i) as RN AutoGenerate 1;

          NEXT

          set errormode = 0 ;

          for i=1 to NoofTables()-1

          ////////////////////////////loop the tables and joinion them to the Data table ///////////////////////

          let vTableName =  FieldValue('TableName', $(i));

           

           

          join (Data)

          load * resident  $(vTableName) ;

          drop table $(vTableName);

          next

           

           

          drop Table Tables;

           

           

           

           

          /////////////////adding raw data dim attirbutes////////////////

          left join (Data)

          LOAD distinct ReportType,

              RecordNo.

          FROM

          [C:\Community\Book1.xlsx]

          (ooxml, embedded labels, table is Sheet1);