There are often occasions in market research when you look at your survey data and find that in order to analyse certain questions properly, you’ll need to manipulate your data. For example, you may need to merge questions together. In this post, I’ll show you how to use Q to merge your data without having to manually write syntax.
Q’s Merge Questions function
The most helpful function in this case is called Merge Questions. To use it, you simply need to go into the Variables and Questions tab, highlight the questions you wish to merge, right-click and press Insert Ready-Made Formula(s) > Merge Questions. Q does the work for you!
This function works with single and multiple response questions, or Pick One and Pick Any questions. It also matches on labels so it is important to ensure consistent labeling, otherwise it will treat different spellings as different code options and append them to your merged question rather than automatically merging them for you. A common case for using this function is to merge ‘First mention’ with ‘Other mentions’ to create a ‘Total mentions’ question. It is important to note that if you merge with a Pick Any question, you will produce a Pick Any, but if you merge a Pick One with a Pick One, you will produce only a Pick One.
Merging non-mutually exclusive data
In the example below, I have two Pick One questions, Q1 and Q2, in which some respondents have answered one question only and some have answered both. I wish to merge these questions together and both have identical code labels. As already mentioned, I only need to select the two questions and use the Insert Ready-Made Formula(s) > Merge Questions option to create ‘Merged Q1 + Q2’.
When I bring this merged question up as a table, I can see that it has included an extra code option called BOTH INPUT QUESTIONS HAVE DATA. If this code has any cases in it, then it means the questions being merged had overlapping data and therefore should not be merged into a single variable. If there is no data here, this option can be removed instead by right-clicking it in the table and selecting Remove.
If your data is not mutually exclusive and is in Pick One format, then it is best to first convert one of your questions into a Pick Any via Insert Ready-Made Formula(s) > Questions -> Pick Any with All Combinations. This will then ensure that Q merges your data correctly as a multiple response question.
Merging Tie-breaker or “Most often” questions with a previous multiple response question
First create a filter for those with a single brand at Q3 using the following steps:
- To count the number of responses at Q3, go into the Variables and Questions tab, highlight the Q3 variables, right-click and select Insert Ready-Made Formula(s) > Mathematical Functions (by Case) > Sum > Yes
- Then change the Variable Type for this sum variable to Categorical and bring it up as a table so that it shows a percentage of counts.
- Now, right-click the % cell adjacent to the ‘1’, right-click, and press Create filter to create a filter for single response records. I have chosen ‘Q4 single brand’ as the name here.
Next, we will filter Q3 by ‘Q4 single brand’:
- Go to Automate > Browse Online Library > Filtering > Filter One Question by Another Question to run the filtering script.
- Select Q3 as the question you want to filter on the first screen.
- Select the filter variable ‘Q4 single brand’ on the second screen.
- Press Yes to filter all question variables by the specified filter.
This will produce a table of Q3 filtered by ‘Q4 single brand’ at the bottom of your Report tree for those with only one response.
Now we will merge the filtered Q3 with Q4:
- Q will match based on the variable and value labels so you should first ensure the labels in both questions are consistent and match for all the options you want to merge together.
- Highlight these two questions in the Variables and Questions tab, right-click and select Insert Ready-Made Formula(s) > Merge Questions > Yes/No (depending on whether you wish to hide the original questions from the blue and brown drop-downs).
This will then produce a merged Pick Any question.
Author: Oliver Harrison
After completing a PhD in German history and literature, Oliver swapped old dusty books for computer screens and logic. He then enjoyed the next 10 years as a survey programmer and data analyst in the Australasian market research industry. Today Oliver is passionate about problem-solving and helping customers achieve their goals as a member of the Customer Success team at Displayr.