topic Re: Read Data From Excel With Transformations in QlikView

Read Data From Excel With Transformations

rodrigue_saade — Fri, 20 Nov 2020 18:02:30 GMT

Hi,

I have the following excel. I need to know what are the transformation steps that i should do in order to read data from it to Qlikview.

Re: Read Data From Excel With Transformations

robert_mika — Thu, 02 Jul 2015 07:53:59 GMT

Is hard to help you based on screenshot

If you post your file that would be better.

In the meantime try Tray to play with Transformation Wizard

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Re: Read Data From Excel With Transformations

rodrigue_saade — Thu, 02 Jul 2015 08:01:19 GMT

Hi Robert,

Thank you for your help.

Attached you can find the excel file.

One more question: is it possible to do the same transformations in Qlik Sense?

Re: Read Data From Excel With Transformations

jagan — Thu, 02 Jul 2015 08:08:11 GMT

Script is same in both Qlikview and Qliksense, so it is possible in Qliksense too.

Regards,

Jagan.

Re: Read Data From Excel With Transformations

robert_mika — Thu, 02 Jul 2015 08:25:59 GMT

For the IS File:

LOAD F1 AS [Statement of Comprehensive Income],

[2013.000000] as [2013],

[2014.000000] as [2014],

[2015.000000] as [2015],

[42005.000000] AS [JAN-15],

[42036.000000] as [FEB-15],

[42064.000000] as [MAR-15],

[42095.000000] as [APR-15],

[42125.000000] as [MAY-15]

FROM

(ooxml, embedded labels, table is IS, filters(

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

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 13)),

Remove(Row, Pos(Top, 14)),

Remove(Row, Pos(Top, 15)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 1))

));

Create PivotTable add Statement of Comprehensive Income as Dimension and the other columns as Expression.

You can Play with the wizard to get more likable solution.

Re: Read Data From Excel With Transformations

rodrigue_saade — Thu, 02 Jul 2015 08:33:17 GMT

Hi Robert,

First thank you for the code.

Second:

Concerning the script you have done, it is for the first sheet in the excel named "IS". It is fine for me.

But, i have a second sheet in the excel entitled "CF", in which i have sections and sub-sections, like "OPERATING ACTIVITIES" and "INVESTING ACTIVITIES" which are sections, and "Adjustments for", "Working Capital Changes" which are sub-sections inside sections. How can read them? should i split them into new fields or i can for example concatenate their names to the corresponding values?

Re: Read Data From Excel With Transformations

robert_mika — Thu, 02 Jul 2015 11:53:08 GMT

One option would be to replace the empty excel cell in column A with different strings or indicate somehow that this is an empty cell.

You can not use If in load statement to replace the null(empty) cell with one sign or string as Qlikview will return only one value anyway.

So the first empty could say "End 1" the second "End 2" and so on.

Not an ideal solution but maybe give you some clues...