Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to transform data while loading it?

Data in the .csv file is not structured properly and it appears like this:

Column1     Column2     Column3     Column4     Column5     Column6

A                    1               C               3               E                   5

B                    2               D               4               F                   6

While loading the data, I want to transform both row and column data simultaneously and appear as below:

Column1     Column2     Column3     Column4     Column5     Column6

A                    B              C               D               E                   F

1                    2               3               4                5                   6

Can you please suggest an appropriate way to achieve this transformation of data.

Thanks!

Tags (2)
1 Solution

Accepted Solutions
mkelemen
Not applicable

Re: How to transform data while loading it?

Hi,

what you need is to unwrap it first and then transpose.

Try this code, it shoul do what you need. You just need to name the fields correctly at the end and you should be all set.

LOAD @1,

     @2,

     @3,

     @4,

     @5,

     @6

FROM

[test.csv]

(txt, codepage is 1252, no labels, delimiter is ';', msq, filters(

Remove(Row, Pos(Top, 1)),

Unwrap(Col, Pos(Top, 5)),

Unwrap(Col, Pos(Top, 3)),

Transpose()

));

Hope it helps.

  Matus

3 Replies
ronnie-warner
Not applicable

Re: How to transform data while loading it?

I guess you're trying to effectively un-pivot the data?

Try using the transpose function. This will rotate the data using the chosen column as the field name and turn other columns into the values.

F1 and search transpose.

mkelemen
Not applicable

Re: How to transform data while loading it?

Hi,

what you need is to unwrap it first and then transpose.

Try this code, it shoul do what you need. You just need to name the fields correctly at the end and you should be all set.

LOAD @1,

     @2,

     @3,

     @4,

     @5,

     @6

FROM

[test.csv]

(txt, codepage is 1252, no labels, delimiter is ';', msq, filters(

Remove(Row, Pos(Top, 1)),

Unwrap(Col, Pos(Top, 5)),

Unwrap(Col, Pos(Top, 3)),

Transpose()

));

Hope it helps.

  Matus

Not applicable

Re: How to transform data while loading it?

Thank you, it is really helpful.

Just to add to discussion: I noticed that while loading data if you get into "Enable Transformational Step".

It gives you visual interface to modify/transform the actual data without the need for you to code and remember the syntax.

From there you can unwrap and transform data. In addition, you can add/delete rows and columns.

- Rajat