I have been given a large datset to sort out, consisting of 38 questions Q1-Q38 all with an answer of Strongly Disagree, Disagree, Not sure, Agree, Strongly Agree.
I need to convert all of the questions to numerical values 1-5 unfortunately some of the questions a negatively weighed eg for Q1 Disagree is 2 but for Q2 it could be 4.
I have a mapping table that has each Q number as either + or - and a second mapping table that has the answers listed 1-5 and 5-1.
How do I get a query to look at the data, see if a question is + or - and apply the relevent numerical score?
thanks
John
Hi John,
I played around with your data a bit and believe the attached file now contains the basis for further analysis. See if you can follow the transformations I did.
First unpivot tblData, merge with tblLinks and expand. Finally, merge with the unpivoted tblWeights and expand.
Loaded the result to sheet tblData and created a straight-forward pivot table from it. See if this works for you. If not, come back here.
Riny
Thanks Riny, thats perfect, I still get confused with unpivoting etc
Glad I could help!
