I'm new to QlikView and have been asked to look at how we can achieve the following...
We have some data that is held and largely managed by a 3rd party company. All the data is based on website input forms.
On the forms are a lot of questions that require the user to tick one or more check boxes per question.
So a typical couple of questions would be...
Q1 Job Function - where the user can then tick one only of the following:
Community Staff Nurse
Q2 Specialist Areas - where the user can then tick any number of the following:
We want to receive a weekly data feed (xls file of 100,000+ records) of user data that we can represent in QlikView. The xls file columns are fine (forename, surname etc) except for the last column that holds ALL the check box questions answers in the format below (QuestionName & Code - delimited by commas and/or pipes):
So here where it says MFNJOBFUNCTION=06 we can see from their table structure below relates to Specialist Nurse. In the MFNSPECIALISTAREAS part of the first record the user has ticked three check boxes which as we can see above are seperated by pipes (i think).
Typical table layout for check box questions below
Job Function Table
Choice Col Prompt
01 001 District nurse
02 002 Nurse practitioner
03 003 Practice nurse
04 004 Health visitor
05 005 Community staff nurse
06 006 Specialist nurse
07 007 Nurse consultant
08 008 Clinical nurse
09 009 Other
I hope that's clear so far!?
Q: How can i extract that columns data so as to present each check box question usefully within QlikView?
Would i be better trying to get the 3rd party company to restructure the xls file they want to give us? There must be another table (e.g. Question) that links these check box answers back to a person in their database, perhaps they can break down the check box answers into seperate xls sheets?
We can create static tables in SQL Server or whatever for Job Function, Specialist Areas etc to map to their codes if need be.
Or should i use String Functions such as SubField to 'pluck' out each check box answer? My understanding is there are a lot of check box questions asked per person and some people will not have answered some questions which will mean no entry in the offending xls column for that particular question.
I hope i've made that clear, like i say i'm new to QlikView so any advice much appreciated.
I'll look into the crosstable option but all the check box answers for each person are in one cell in the xls (the other cells in each row are fine and hold only one bit of data such as forename). Is it possible to pull each check box answer out from that format using a crosstable?