recursive multi-row question

Hi, I have a list of data that I need to concatenate. The data is in one column. I need to create a second column that concatenates it like this.

What I have What I need

BS-831271 -> BS-831271
BS-831271.01 -> BS-831271 \ BS-831271.01
BS-831271.0101 -> BS-831271 \ BS-831271.01 \ BS-831271.0101
BS-831271.010102 -> BS-831271 \ BS-831271.01 \ BS-831271.0101 \ BS-831271.010102

For each value, I need to look at the previous row(s) and see if the value above is contained in the current row. If it is, I need to concatenate those values together. This gets tricky when I look back more than 2 or 3 rows -potentially I need to look back at hundreds of rows prior. Does anyone know the best way to accomplish this? I assume it's a Multi-row but I'm having problems implementing.

Thanks in advance.

Common Use Cases

Best Practices

Accepted answers

NicoleJohnson

Ah, okay, that might require a different strategy. Take a look at the attached.

I first split out the base part so that I could do a join on all records that match for the first portion (BS-00163, for example). Then I use a Filter with a Contains formula to determine if one field is contained within the other. I then summarized using the Concat function with a " / " delimiter to create the summarized list.

Hope that helps!

bb213Solution2.yxmd

All comments

NicoleJohnson

Try this formula in your Multi-Row formula tool - uses RegEx to determine if the last record in the concatenated string from the row above is contained in the current row's field.

if isnull([Row-1:NewField]) then [Field1]

elseif Contains([Field1],RegEx_Replace([Row-1:NewField],".*\s\\\s([A-Z]+\-[0-9.]+)$","$1"))

then [Row-1:NewField]+" \ "+[Field1]
else [Field1] endif

Let me know if that works!

Cheers,

bb213Solution.yxmd

bb213

Nicole,

Thanks. It appears to work, but does this solution require the Field 1 to be sorted?

Ben

bb213

Thanks Nicole, can I ask a follow-up? I have to break out each group using text to columns.Easy enough. But is there a way to dynamically select the number of columns I need? for example, I know I need 12 columns, because the largest "group" has 12 structures, but is there a way Alteryx can count and select that automatically?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703