Help loading in spreadsheet

[hopkinsc]

Hi all,

I have the attached spreadsheet containing multiple choice questions and answers given by a number of companies. i am looking for help to try and get this loaded into QlikView.

The result i want is to be able to count how many people gave any specific answer (note that some questions have multiple answers too).

at the moment the format of the spreadsheet cannot be changed, you will notice that under some questions there is also a 'comments' line but not for all, this will probably have an impact on the load.

any help would be appreciated.

Many thanks in advance.

[dgreenberg]

The problem is with Excel not with QlikView.

You would have equal if not greater problems trying to bring this data into any database or BI tool.

I suggest creating an Excel macro to reformat the data into a new tab in a columnar way that will allow it to easily be brought into another tool.

Alternatively you could write a very specific load script but that will need to be modified as new answers, companies, questions are added.

[ciaran_mcgowan]

Ugh, I have to deal with badly organised data all the time

It's not impossible but it is awkward. See my attached attempt to get you started. I've two approaches for the initial load of data: the 1st (Main tab of script) is literally pulling the all data from your excel, filling in missing data. This approach will need to be amended if more questions are added so it would be a pain to use. The 2nd attempt (Alternative tab of script) is a much better approach so I would use that instead (Note: I didn't have time to test this script).

The second step is to create 2 tables; 1 for Questions & one for Answers (also created a QuestionID field to join them).

The final step is to load all company answers into a single field, with flags you can use to distinguish which company gave which answers. You should now be able to find the answers you need in the front end yourself. Good luck!

[ciaran_mcgowan]

I forgot to mention that I also separated out each answer so if one answer contains 3 different answers, then that results in 3 individual rows.

[rwunderlich · Accepted solution]

The Transformation wizard in the script editor can be very useful. I did the initial load of the spreadsheet via the wizard without typing a single line of script. I then manually added scrip to parse the multiple responses into individual rows.

If you want some good exercises on using the transformation wizard, check out the book "QlikView 11 for Developers".

Solution attached.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[ciaran_mcgowan]

Hi Hopkinsc,

Did my QVD help you at all?

[hopkinsc]

thanks for your help everyone, i went with Robs suggestion which has worked well.