Survey Application

[#1]

Hey guys,

I'm making an application using QlikView to analyze results from a survey.

I have received the survey results, but instead of names for the questions they have used codes.

I have another list of codes with the full question next to it.

Now my question is how do I join these two together, I'm driving myself crazy to find some sort of a solution, I know this will be fairly easy, but I can't seem to join these two together.

So that I get a listbox in Qlikview in which I can select all my questions and still be able to do calculations with my answers.

I have included an excel sheet explaining my problem closely.

I've tried looking for any survey applications on this forum, but without any luck

hope you guys could help

thanks,

Regards

/Sajjad

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/5047.Survey_5F00_problem.xlsx:550:0]

[#2]

Hi Sajjad_m

You need to use the transformation wizard when you bring in the Answer table. It will help you build a CrossTable.

Here is the code that should give you what you are looking for. Note that I moved the Answer table to a new tab called Answer. I also attached a zip file with a QVW and modified spreadsheet.

LOAD QuestionCode,
[Question Full]
FROM
[5047.Survey_problem(1).xlsx]
(ooxml, embedded labels, header is 1 lines, table is Question);

CrossTable(Column, Answers)
LOAD @1 as QuestionCode,
@2,
@3,
@4,
@5,
@6,
@7,
@8,
@9,
@10,
@11
FROM
[5047.Survey_problem(1).xlsx]
(ooxml, no labels, header is 1 lines, table is Answer, filters(
Transpose()
));

[#3]

Hi,

If you have a table with only question ID ans question NAME and you want to have the name of the question in your first table with only the ID of the question, the best way is to use the ApplyMap() function : ApplyMap( 'nomdemappage', expr, [ , defaultexpr ] )

First, you have to load your table (ex. QUESTIONS) with ID and NAME with a MAPPING LOAD

In the LOAD for the second table, use ApplyMap function to retrieve the NAME of the question passing the ID : ApplyMap('QUESTION',ID)

At the end of the script, you may drop you mapping table QUESTIONS

Regards

Oliver

[#4]

Hey bwh,

Your solution worked out fine!

Now however, I'm stuck with an other problem. How do I count my answers. say I want a count of where my answers are a and a different count of where my answers are b.

Then I want to subtract my b answers from my a answers.

My (solution):

I created a textfield with the expression: =if(Answers = 'b', count(Answers))

This however doesn't work.

What am I doing wrong ??

Hope you can help me out

Regards

/Sajjad

[#5]

Hi Sajjad_m

Read the help on using Set Analysis.

In order to count the 'b' answers you could use this in a text box: =count( {1<Answers= {b} >} Answers )

You could also add a counter field in the Answer table which would let you use basic count or sum calculations in your tables. For example:

Question:
LOAD QuestionCode,
[Question Full]
FROM
[5047.Survey_problem(1).xlsx]
(ooxml, embedded labels, header is 1 lines, table is Question);

Temp:
CrossTable(Column, Answers)
LOAD @1 as QuestionCode,
@2,
@3,
@4,
@5,
@6,
@7,
@8,
@9,
@10,
@11
FROM
[5047.Survey_problem(1).xlsx]
(ooxml, no labels, header is 1 lines, table is Answer, filters(
Transpose()
));

Answer:
load
QuestionCode,
Answers,
1 as Counter
Resident Temp;

drop table Temp;

[#6]

O and to answer your question on the if statement....

You have need the aggregation before the if

=count(if(Answers = 'b', Answers))