Exporting Coded Verbatim Responses (Topics) | XM Community
Solved

Exporting Coded Verbatim Responses (Topics)

  • 13 December 2017
  • 3 replies
  • 38 views

Userlevel 1
Hi All,

Does anyone have any experience with coding verbatim responses (what Qualtrics calls Topics in Text Analysis/TextiQ) and then exporting these topics.

It seems to export all the topics for each individual respondent into one field (or one cell in Excel). To actually make analysis easier, what you really need is a separate column for each code (ideally with a 1 or 0 - but even just the text in separate columns would help).

For example, if I have 3 Topics:
Safety
Technology Benefits
Environmental Benefits

If these are all coded for a response by one respondent, what you get is:
Safety, Technology Benefits, Environmental Benefits in one cell in Excel.

You can use the Text to Columns function in Excel, which will separate these into separate columns... but the problem is if you have another respondent whose response was coded to: Safety, Environmental Benefits

Then you will have a column which has Safety for each respondent, but the next column will have Technology Benefits for the first respondent and Environmental Benefits for the second respondent....

So there is more manual sorting of the data required in Excel.....

Anyone found a way to overcome this yet?

When exporting the data I did select: Split multi-value fields into columns, but this doesn't seem to work for Topics.

Any help greatly appreciated.

Thanks,
John
icon

Best answer by AnthonyR 14 December 2017, 00:04

View original

3 replies

Userlevel 7
Badge +7
@john_shearer

Unfortunately, there is no way to split these on export. We process after export for this kind of problem.
Userlevel 1
> @AnthonyR said:
> @john_shearer
>
> Unfortunately, there is no way to split these on export. We process after export for this kind of problem.
>
>

Yes @AnthonyR I feared this was the case....! :-(


In terms of processing after Export - do you have any hacks? I have used:
- exporting to Excel
- Using Text to Columns
- Copying the columns with the separated Topics and Paste/Transpose into a new worksheet (so that each respondent's Topics are now in the same column)
- Then adding the list of Topics in a column in Excel (below the rows that have the Topics) and using IF formulas to look for the relevant Topic in the column above, and recording a 1 if the Topic is present for that respondent. End up with formulas like this: =IF(D$3=$B11,1,IF(D$4=$B11,1,IF(D$5=$B11,1,IF(D$6=$B11,1,IF(D$7=$B11,1,IF(D$8=$B11,1,0))))))

I think using Array formulas would achieve the same end, but that is a bit beyond my Excel skills.

Just had a thought that LookUP formulas may also work.
Userlevel 7
Badge +7
@john_shearer

I think the way you are currently handling it isn't a terrible solution. What you may consider is just making a column for each topic and checking for the "existence" of a topic for a person in the merged column. Since you can then use this repeatable formula, you won't have to nest nearly as much to populate your updated columns.

=IF(ISNUMBER(SEARCH("category",originalColumn)), 1, 0)

Leave a Reply