Calculate mean based on recoded values for survey question
I have a database about the results of a survey and two of those tables in the database are:
(1) Response - it keeps students' responses, question ID, year of responses submitted, etc
(2) Option - it keeps the attributes about question's options, like the text of option, value, etc
For explanation, I have exported some data from these two tables to Excels and prepare a Qlikview (Files are attached).
The "Response" in Response table stores what students answered and the "QO_Key" in both tables link up student's response with the options.
Now I want to create a chart showing the mean of a question. Instead of using the value stored in "Response" field to calculate (which ranges from 1 to 5), I would like to convert it into a scale of 0 to 100 (i.e. 1=>0; 2=>25; 3=>50; 4=>75; 5=>100). This mapping relationship is defined in the field "Option_Calculation" in Option table.
How can I calculate the mean using a scale of 0 to 100 in Qlikview? I tried using dual function before in data loading script, but it seems that it does not work (or maybe I did not use it correctly). Thanks.
