Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transpose Excel in script load

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

A

FIG 1

Test v3.bmp

FIG 2

test 1v2.bmp

1 Solution

Accepted Solutions
Sunil_Kenth
Employee
Employee

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.

View solution in original post

6 Replies
Sunil_Kenth
Employee
Employee

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.

View solution in original post

Miguel_Angel_Baeyens

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

Not applicable
Author

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

Not applicable
Author

Thanks Guys

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

Kind regards

Anne

Not applicable
Author

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

Not applicable
Author

Its ok figured it out Conditional delete

Thanks