Joining different question types in a survey

[nivi_s]

Hello!

I am trying to automate the analysis of a survey - for context, we have a dataset of all the questions, the options, and the codes that are used to encode/decode the data. I have 5 years worth of survey responses that have been coded in different ways by different people, and I am establishing a way to automate the processing to standardize them as well as future surveys. I have a question order dataset for each year to match with those in the survey responses, as well as a master dataset to categorize the questions based on the analysis objectives - they have the question and answer code numbers in common.

The issue I am running into is, the survey has multiple choice questions, Likert scale questions, numeric entry questions, and text entry questions. If I Join them with both identifiers, the numeric and text entry questions are lost. If I join them with only one, there is too much duplication. I cannot join by 2 and then use a Union later, because it does not fully capture the other fields in the master datasets.

I made some fake data for this and attaching it as a .yxmd file.

I have a brute force way to do this with joins, unions, and filters, but I'm wondering if there's a more elegant solution - is there a tool that will capture partial matches? Am I overthinking this and/or missing something glaringly obvious?

Thanks!

Nivi

[danilang · Accepted solution]

Hi @nivi_s 

In your surveys, as you mentioned, you've got 2 distinct types of questions, Type A, multiple choice answers like Q1 and Q3, and Type B, free text like Q2_1 and Q2_2.  If you treat the two types separately you can get by with a very simple workflow.

The trick here is the order in which you process the questions.  The 1st join is on Question and Response so it pulls out all the type A questions.  The type B questions aren't matched so they exit the L output of the 1st join.  The second Join tool takes these and matches them to the questions on Question only.  The 2 groups pass through the union and are clean and sorted by the remaining tools, giving you

Dan

[nivi_s]

Hi @danilang 

Thank you so much!

(In hindsight, that solution is so straightforward, I can't believe I didn't think of that.)

Have a wonderful weekend!

Nivi