3 Replies Latest reply: Feb 3, 2016 11:55 AM by siva reddy RSS

    Data transpose

    siva reddy

      hi

      Please find the attached.

      The first tab contains the raw data and I want the output results exactly as in the "output" tab.

       

       

      articleid is the key here.One article id will have multiple rows."ColName" field should be there in the column and "ColValue" should be in the respective rows for each articleId.

       

       

      I want to use your own creativity to come up with the dynamic logic which should accommodate n number of columns/rows for each articleid.i.e I don't want you to simply import as a cross table.

       

       

      Can any one help me.

       

      regards

      reddy

        • Re: Data transpose
          Marco Wedel

          please create a shorter, clearer example and elaborate on your requirements.

           

          thanks

           

          regards

           

          Marco

          • Re: Data transpose
            Settu Periyasamy

            Hi Siva,

            I used the below steps for your file..

             

            1. Load Your Excel file ,(Store it as a Qvd , So, you can use it future, instead of loading Excel)

            2. Create the Generic Load for transforming The Generic Load

            3. Join All your tables into single table..(Check the rwunderlich reply, in the above mentioned Document)

            That's it..

            /******* LOAD THE BELOW SCRIPT FROM THE EXCEL - STORE THE TABLE AS QVD FILE  ****/
            
            T1:
            LOAD AutoNumber(StructureID&AtricleID,RecNo()) as Key,
              RowID, 
                 ColID, 
                 StructureID, 
                 AtricleID, 
                 ColName, 
                 ColValue
            FROM
            [Task - 02022016.xlsx]
            (ooxml, embedded labels, table is Data) Where isnum(RowID) and IsNum(ColID);
            
            T2:
            Generic LOAD AtricleID,ColName,ColValue Resident T1;
            
            CombinedGenericTable:
            Load distinct AtricleID Resident T1;
              
            FOR i = NoOfTables()-1 to 0 STEP -1
              LET vTable=TableName($(i));
              IF WildMatch('$(vTable)', 'T2.*') THEN
              LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
              DROP TABLE [$(vTable)];
              ENDIF
            NEXT i
            
            
            DROP Table T1;
            

            Check the Attachment..