1 Reply Latest reply: Dec 10, 2014 7:04 PM by Srikanth P RSS

    Help Transformation steps in Excel import wizard. Changing formatting

    Darrin Pilkington

      Hello,

      I have Financial files in Excel I am trying to pull from that look like the top portion below.  To make use of the data I need to grab the Product A and B headers and align it with the sales and returns as well as grab the Gross Sales and Return headers and align them next to the appropriate rows as seen in the lower section to make this usable in QlikView.

       

      There are some transformation steps which mention Fill, Column, Uwrap and Rotate.  The Help file doesn't give much information that's useful.

       

      Is this possible?

      Thank you.

       

        • Re: Help Transformation steps in Excel import wizard. Changing formatting
          Srikanth P

          Hi Darrin, Please find the attached qvw as reference.

           

          I created mapping table for field names.

           

          #################################################################

           

          TEMP:

          LOAD

            RowNo() AS RN ,

            * ,

            IF( PRODUCT_FLAG , A , Peek(PRODUCT)) AS PRODUCT ,

            IF( GROSS_RETURN_FLAG , A , PEEK(TYPE) ) AS TYPE

          ;

          LOAD

          // RowNo() AS ROW_NO ,

            A,

          //     B,

               C,

               D,

               E,

               F,

               G,

               IF( WildMatch(A,'Product*'),1,0) AS PRODUCT_FLAG,

               IF( WildMatch(A , 'gross*sales','returns') , 1,0) AS GROSS_RETURN_FLAG

          FROM Comm14533.xlsx (ooxml, no labels, table is Sheet2);

           

          FIELDS_MAP:

          Mapping

          LOAD * Inline [

          KEY , NAME

          C , 13-Aug

          D , 13-Sep

          E , 13-Oct

          F , 13-Nov

          G , 13-Dec

          ];

           

          FINAL:

          NoConcatenate

          LOAD

          // RN,

            PRODUCT ,

            TYPE ,

            A,

            C,

            D,

            E,

            F,

            G

          // GROSS_RETURN_FLAG

          Resident TEMP Where ( PRODUCT_FLAG = 0 AND GROSS_RETURN_FLAG = 0 );

           

          DROP Table TEMP ;

           

          RENAME Fields using FIELDS_MAP ;