Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Parsing string data and matching to certain rows

Shelbey
Alteryx
Alteryx

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. 

6 REPLIES 6
apathetichell
18 - Pollux

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

Shelbey
Alteryx
Alteryx

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

 

Shelbey_0-1619471118664.png

 

apathetichell
18 - Pollux

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
14 - Magnetar

Hi @Shelbey 

 

Does this look correct? Workflow also attached.

jrgo_0-1619472418731.png

 

Shelbey
Alteryx
Alteryx

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
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
Labels