Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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?
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