Loop through columns within one Data set

[G1]

Hello All,

Please could someone tell me if It is possible to do the following (please see pic for explanation)? And if so how might be the best way to do this? (alteryx/programming experience = beginner/early intermediate)

The formula tool works but, due to the amount of times I need to repeat this process it would take a long time to keep manually adding a new column and entering in a new formula. I also tried the multi-field formula tool, which does automatically create the new columns I would like but only for one formula so I end up with the same values in each column.

Any advice/solutions would be appreciated.

Thanks!

[MarqueeCrew · Accepted solution]

@G1,

I saw your problem last night before watching a movie and heading off to sleep.  I'm glad to see that it was here for me still in the morning and that @BenMoss and @LordNeilLord hadn't swooped in to solve this fun problem.  I used to play a game where we used the alphabet and everyone had to add a food that began with the letter A and then proceed to recall all named fruits before us and add a new fruit to the list (e.g. Apple, Apple Banana, Apple Banana Cupcake, ...).  That game is how we solve your challenge.  

• Add a RecordID (Sequence)
• Transpose the data from fields to rows
• Create an answer (new value) of the prior new value + space + current value
• Cross Tab the result using the Field # as the header and the new value as the "value"
• Remove the RecordID
• Browse the desired results

No looping, just running through the data once.

Cheers,

Mark

[LordNeilLord]

I was away this weekend @MarqueeCrew..
Back to normal business tomorrow :)

[G1]

Hi Mark,

Thank you so much for getting back to me!

I'm working through your solution and I'm stuck at the Cross Tab tool; how did you get it to separate out the row results? I've attached what I've created and it's not like yours.

I've also attached the test data I'm using and my Multi-Row Formula tool. Re. the latter, is that how you did your formula? Or is there a way to make that more simple?

Thanks again!

[ThizViz]

"...and that @BenMoss and @LordNeilLord hadn't swooped in to solve this fun problem"

Hahahahahaha you have some competition, @MarqueeCrew!

[MackStewart]

Hello G1

Not sure if you ever got a solution.

Here is my offering.

Basically, you need to split your workflow, then bring it back together with a  join.

(Now let's hope I find the answer to the question that led me here. :))

Karen

[G1 · Accepted solution]

Thanks Karen,

I did end up getting it working, thanks to the help I found here and general fiddling on my part. I used a multi-row formula tool and then a cross-tab tool (i've added pics of the tool configurations below in case you need it). What was your problem? I've gotten a little better at Alteryx now so maybe I can help.

G1

[SriKoka]

I really like this workflow. Along with showing if the keywords appear in the input dataset, could you please share how we can display the keywords that matched in the input dataset. For eg, if input string is "I am blessed to have this day" and my keywords are "blessed", "day", then I want the output to show that it matched with the following keywords: "blessed", "day".

Thanks for your help.

Sri.

[G1]

Hi Sri,

I am not sure you need a rolling concatenate for this. You can just use the multi-field formula tool and a 'Contains' or 'RegEx' expression. Please see the attached workflow.

Thanks

G1

[SriKoka]

Hello G1,

Thank you for responding. This is very helpful! I have a list of 60 keywords that I need to match and this list will continue to grow in the future. Is it possible to show all matched keywords in one column instead of 60 columns? For eg, in the string "I am blessed to have this day" looking for keywords "blessed", "day", the result of matched keywords would be in one column named "Results" containing all words that matched "blessed, day".

Regards,

Sri.