How to represent this xls data in QlikView?

haymarketpaul — Wed, 27 Apr 2011 14:43:06 GMT

Using QV 10

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:

District Nurse

Nurse Practitioner

Practice Nurse

Health Visitor

Community Staff Nurse

Specialist Nurse

Nurse Consultant

Clinical Nurse

Other

Q2 Specialist Areas - where the user can then tick any number of the following:

Respiratory Medicine

Diabetes

Tissue Viability

Travel Health

Stoma care

Continence

Oncology

Carsdiovascular Disease

Infection Control

Urology

etc

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):

Demographic Codes (xls Column Header)

MFNJOBFUNCTION=06,MFNSPECIALISTAREAS=08 | 09 | 12,MFNINDUSTRYSECTOR=02

MFNJOBFUNCTION=06,MFNSPECIALISTAREAS=02,MFNINDUSTRYSECTOR=02

MFNJOBFUNCTION=06,MFNSPECIALISTAREAS=17,MFNINDUSTRYSECTOR=01

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.

How to represent this xls data in QlikView?

Wed, 27 Apr 2011 14:52:31 GMT

Hi Paul,

i'm not sure to understand you well. But as far as i understand if you've got an XLS file and want to relate every checked box to name and surname, use the Crosstable option while loading the file

best regards

christian

How to represent this xls data in QlikView?

haymarketpaul — Wed, 27 Apr 2011 15:09:20 GMT

Hello

Thanks for the reply.

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?

topic How to represent this xls data in QlikView? in QlikView

How to represent this xls data in QlikView?

How to represent this xls data in QlikView?

How to represent this xls data in QlikView?