Help Transformation steps in Excel import wizard. Changing formatting

[Anonymous]

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.

[#2]

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 ;