Parse Survey data that is Concatenated Display in Colum / Row format

Working on a form that has a form ID for each unique form in row format, and all of the data is concinnated into one cell. I want to break out all the questions into Row Header = Question and Cell value = Answer. Some answers can be blank.

Data is coming from AWS

I have attached the input and the output I desire

Parse out Questions into Columns.yxmd

Datasets

Regex

Parse

Amazon S3

Transformation

Accepted answers

JoeS

Nice little parsing problem there.

Likely to be many ways to achieve it but this is how I set about solving it:

Screenshot 2021-11-23 143450.jpg

I have annotated the tools, let me know if there are any steps you are unclear on 🙂

Parse out Questions into Columns.yxmd

Ben_H

Hi @LAS1,

You can do it by adding a record ID and using that to cross tab.

See attached example (I just duplicated the one input row to try it out)

Regards,

Ben

Parse out Questions into Columns rows.yxmd

All comments

JoeS

Nice little parsing problem there.

Likely to be many ways to achieve it but this is how I set about solving it:

Screenshot 2021-11-23 143450.jpg

I have annotated the tools, let me know if there are any steps you are unclear on 🙂

Parse out Questions into Columns.yxmd

Ben_H

Hi @LAS1,

Nothing really to add here, I just wanted to have a go at it too!

My solution -

Regards,

Ben

Parse out Questions into Columns.yxmd

LAS1

Just what I was looking for. I will admit, I have struggled with this longer than I’m willing to admit!

LAS1

Different way than above, but just what I was looking for!

JoeS

Sometimes you can just get stuck on a problem and can't think around it. Happens to us all.

Glad we were able to help!

LAS1

Hey Joe,

Follow up question for you. I actually have ~ 100 rows of data (hey have unique IDs or we can make them), so when I run the workflow it makes thousands of columns. Is there a way to force the query to move to a new row once the data is finished being parsed?

2-17-2022 7-40-12 AM.png

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703