This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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!
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.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return. Please Subscribe to my youTube channel.