Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load excel data in custom format

Hi,

Attached is a excel sheet where business stores data in a custom format. I have a requirement to load this data into QlikView and create dashboards.

I need data to be transformed into the below format

Row ID, Insurer, Rate, Line

Also business would be adding new insurers at the right (columns) and the script should load them atumatically without any script change.

Could you please help me with the script?

Regards,

Murali

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

Re: Load excel data in custom format

Hi,

You need some transformation:

During the Wizard to load the table, enable transformation step, rotate the type -> Transpose, then use fill on the first column.

Then you use cross table.

After this, I believe you would be able to use the data but to make exactly in your format I used load resident and an outer join.

I just had to adjust the cell F1 of your example, because there was a second value there 'Ascot'. I don't know why.

Hope this helps,

Regards,

Erich

3 Replies
jason_michaelid
Honored Contributor II

Load excel data in custom format

Hi,

Are you able to change the header of this spreadsheet? Can you make the header into a single row and have:

RowID

Insurer1Rate

Insurer1Line

Insurer2Rate

Insurer2Line

Insurer3Rate

Insurer3Line

etc...

If you can do this then the Crosstable() function plus a little jiggery-pokery will achieve what you want.

Jason

Not applicable

Load excel data in custom format

Unfortunately NO.

This file is generated by an automated process and chaging this means a change to the current system which is generating the file. I don't think client will agree for the same.

Are there any other ways to achieve this?

erich_shiino
Honored Contributor

Re: Load excel data in custom format

Hi,

You need some transformation:

During the Wizard to load the table, enable transformation step, rotate the type -> Transpose, then use fill on the first column.

Then you use cross table.

After this, I believe you would be able to use the data but to make exactly in your format I used load resident and an outer join.

I just had to adjust the cell F1 of your example, because there was a second value there 'Ascot'. I don't know why.

Hope this helps,

Regards,

Erich

Community Browser