Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
darrin_pilkingt
Contributor II

Help Transformation steps in Excel import wizard. Changing formatting

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.

1 Reply
Not applicable

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

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 ;

Community Browser