topic Re: Transpose Excel in script load in QlikView

Transpose Excel in script load

Tue, 07 Jun 2011 10:33:21 GMT

Hi Guys,

Hope someone can help, I am in the middle of loading Excel management reports into Qlikview,

There are a few that are formatted incorrectly , at the moment they are formatted like FIG 2 , however I wish for them to be formatted as FIG 1 .

( Basically all the data Transposed !!) I dont really want to do this in Excel (as the management do not want their logs amended and I'm not really keen on building a mirror log to load)

Doen anyone know if Qlikview has some fancy script that may help ?

Thanks

FIG 1

FIG 2

Re: Transpose Excel in script load

Sunil_Kenth — Tue, 07 Jun 2011 10:39:09 GMT

HI,

Have a look at using the transformation step which is available on step 2 of the table wizard when doing the data load.

Thanks,

Sunil

Sunil Kenth

Pre-Sales Solutions Consultant

07557 267133

qlik.com<http://www.qlik.com/>

NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited. QlikTech UK Limited, registered in England and Wales with number 5292408, and whose registered office is Villiers House, Clarendon Avenue, Leamington Spa, Warwickshire, CV32 5PR.

Re: Transpose Excel in script load

Miguel_Angel_Baeyens — Tue, 07 Jun 2011 10:40:34 GMT

Hello,

If I understand you right, a crosstable load would help, so the months are loaded as field month and its different values, column A is loaded as it is and values are loaded as values. Not tested, but the script should look like

Data:
CROSSTABLE (Type, Month, Values) LOAD *
FROM ExcelFile.xls (....);

Then in the new chart, you can pivot the types to be vertical at the top and so.

EDIT: By the way, transposing is available for QVD files as well (actually, for any table file), so you can load your excel files, store into QVD, then do the transposing when loading the QVDs. I don't see any particular advantage in this double step, but you may find it useful.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Transpose Excel in script load

Tue, 07 Jun 2011 10:43:23 GMT

Hi,

You can use the Cross Table Wizard to convert the tables outputs from Figure 2 to Figure 1 by which your columns would be converted to rows or vice versa.

Regards,

Raj Kishor

Re: Transpose Excel in script load

Tue, 07 Jun 2011 10:48:10 GMT

Thanks Guys

Both answers seem to work , going to flute around with data to see what model suits it best

Kind regards

Anne

Transpose Excel in script load

Tue, 07 Jun 2011 11:15:41 GMT

Hi Guys

Just wondering on the Transpose ( using the file wizard) does anyone know how to select the data from the third row down ?

Thanks

Transpose Excel in script load

Tue, 07 Jun 2011 11:20:14 GMT

Its ok figured it out Conditional delete

Thanks