ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Parsing string data and matching to certain rows

Shelbey
8 - Asteroid

Hi Community!

 

I'm in need of some advanced parsing tips and tricks 🙂 

 

I have a data set from a safety inspection. Each inspection sheet allows you to inspect up to 10 different areas on a single submission. All of the data gets entered into one cell and separated by commas. I've been parsing the data so that if a person inspected 7 areas, the questions and answers for those 7 areas are parsed to match each area. So far everything has been going great until I got to the comments section of the inspection data.

 

In the attached data set, there are two inspection sheets (identified by the submission number), each with 7 areas. I've already parsed the data into the 7 areas and filtered out only one category for easy viewing. For each area listed, the employee answered Yes or No to whether or not there were health hazards present. You can see the responses before and after parsing. If the employee says Yes, they are then required to enter comments on what hazards they found and what they did to mitigate them. I can't just have a simple formula where IF [Response] = 'Yes' then [Comments] else Null() because there may be more than one set of comments. To show this, I also went ahead and parsed out the Comments. One submission has 2 areas with comments, the other has 3.

 

The problem I need help with:

If the string of responses is something like, "No,No,No,No,Yes,Yes,No" and I have Comments1 and Comments2 after parsing, I need to make sure the Comments1 comment only shows up next to the first "Yes" in the string, and the Comments2 comment shows up next to the second "Yes" in the string. 

apathetichell
13 - Pulsar

Does this work? It matches the comment number (ie 1-3) with the row number (ie 1-7)

Shelbey
8 - Asteroid

Not quite... I need those comments shifted down so they line up with these two "Yes" responses.

 

Shelbey_0-1619471118664.png

 

apathetichell
13 - Pulsar

O.k. - I think I'm seeing it now - the maximum comments would be 7 - but 3 is the number it's currently split to.

jrgo
13 - Pulsar

Hi @Shelbey 

 

Does this look correct? Workflow also attached.

jrgo_0-1619472418731.png

 

Shelbey
8 - Asteroid

This will work! I'm probably going to have to filter out each question, do this WF, then put it all together with a Union tool, but that is still better than what I was trying to do. Thank you for your help!

MarqueeCrew
19 - Altair
19 - Altair

@Shelbey ,

 

Little did I know that the resolution to your challenge included opening pandora's box.  Assignment of hazards into their logical positions (hazard 1 ... N) for the Y/N responses and then finding the relative comment from a delimited list was so much fun.  For the naked eye or for the review of small amounts of data this format makes perfect sense.

 

Knowing that the source of data was a questionnaire and that the storage of the data (and subsequent analytics) didn't include any data normalization were fun facts to learn.  Alteryx has a way of smoothing out these wrinkles and in your case, you unscrambled the omelet.

 

Thanks for the opportunity to work with you on it.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
Labels