Load excel data in custom format

[#1]

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

[Jason_Michaelides]

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

[#3]

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?

[erichshiino · Accepted solution]

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