Read Data From Excel With Transformations

[rodrigue_saade]

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.

[robert_mika]

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)

[rodrigue_saade]

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?

[jagan]

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

Regards,

Jagan.

[robert_mika]

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, 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.

[rodrigue_saade]

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?

[robert_mika]

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...