I am looking for the best way to make reports in qlikview from data retreived by a market research company. This data is a bit tricky to organize in qlikview, because the answers given by all respondents have to be linked to questiontext and answers. I'm looking for a optimal solution for these kind of reports. Here is an example of typical data source:
The data is stored in a tabseparated og semikolonseparated file, like this:
IDNUMBER COUNTRY YEAR MONTH DAY Q15 Q16 WEIGHT Q1 Q2C01 Q2C02 Q2C03 Q2C04 Q2C05 Q2C06 Q2C07 Q2C08 Q2C09 Q2C10
1 1 2010 07 05 1 52 10000 4 1 0 0 0 0 0 0 1 0 0
2 1 2010 07 05 1 34 10000 10 1 0 0 0 0 1 0 1 0 0
3 1 2010 07 05 2 49 10000 4 0 0 0 0 0 1 0 0 0 0
The columns name are the question name from the survey, and the each line represents what one respondent has answered for each question. Now, the problem is that this file does not hold any information about the question text, or which answers the respondents had to chose from. For each question there is a text, and an answerlist. These can be defined in a excelsheet.
I've tried solving this by creating a crosstable for the data, with the columns ID (qualified field), VarName and Data. The excelsheet, that defines the question texts and answerlists, have the columns VarName, questiontext and answerlist. Each question is repeated the same amount of times as there are answers.
This is an example of the excelsheet:
VarName
Answerlist
Questiontext
D1
Committed
Commitment segments
D2
Uncommitted
Commitment segments
D3
Open non-users
Commitment segments
D4
Unavailable non-users
Commitment segments
Q2C01
Answer1
Which coffee brands have you ever heard of?
Q2C02
Answer2
Which coffee brands have you ever heard of?
Q2C03
Answer3
Which coffee brands have you ever heard of?
Q2C04
Answer4
Which coffee brands have you ever heard of?
Q2C05
Answer5
Which coffee brands have you ever heard of?
Q2C06
Answer6
Which coffee brands have you ever heard of?
Q2C07
Answer7
Which coffee brands have you ever heard of?
Q2C08
Answer8
Which coffee brands have you ever heard of?
Q2C09
Answer9
Which coffee brands have you ever heard of?
Q2C10
Answer10
Which coffee brands have you ever heard of?
Q2C11
Answer11
Which coffee brands have you ever heard of?
The data in the datacolumn is manly coded as 1 for answer and 0 for no answer. And in the charts, most of the expressions are: sum(Data). And the listbox contains the questiontext.
This works fine for small surveys with few respondent. But when the datafile contains over 50.000 records, and the survey has a lot of queston, the datatable gets extremely large, and any calculation takes a lot of time. This model also causes qlikview to run out of memory, when complex calculations are needed.
Would be greatful for any tips regarding this kind of reporting.
