Help Transformation steps in Excel import wizard. Changing formatting

Anonymous — Wed, 10 Dec 2014 21:48:05 GMT

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

Thu, 11 Dec 2014 00:04:17 GMT

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 ,

// B,

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 ,

// GROSS_RETURN_FLAG

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

DROP Table TEMP ;

RENAME Fields using FIELDS_MAP ;

topic Re: Help Transformation steps in Excel import wizard. Changing formatting in QlikView

Help Transformation steps in Excel import wizard. Changing formatting

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