recursive multi-row question

[bb213]

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. 

[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,

NJ

[bb213]

Nicole, 

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

Ben

[NicoleJohnson]

@bb213, yes the data would need to be sorted for the comparison to work. But if you need it to go back into the original pre-sorted order after the comparison, you could add a RecordID before the Multi-Row tool, Sort the data, perform your comparison, and then Sort the data by RecordID to put it back into the original order. :)

NJ

[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? 

[NicoleJohnson]

I'm not aware of a way to dynamically select the number of columns, but you can change the Text to Columns to split by rows instead, and then use a Cross Tab tool to pivot the information back into having your data in columns. Does that make sense? You'll want to make sure you have a RecordID or some other grouping information prior to your Text to Columns tool so that you can pivot the information back grouping by RecordID etc.

Let me know if you need more info on this! 

Cheers,

NJ

[bb213]

Nicole, I found an example where your solution doesn't work. 

RIGHT MOST                              Project Structure
BS-001613.0101010112              BS-001613.0101010112
BS-001613.01010101121            BS-001613.0101010112 \ BS-001613.01010101121
BS-001613.01010101122            BS-001613.01010101122

The last one should be "BS-001613.0101010112\BS-001613.012010101122".  In other words, I need to look back at the N-2 row or at the first structure in the series. Any idea how I would do that?  I appreciate all your help. 

Ben

[bb213]

Is there a way that it will loop through all rows to find a match? 

[NicoleJohnson · Accepted solution]

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! 

NJ

[bb213]

YES! THANK YOU!