Concatenating unknown number of subsequent rows

[jamesgough]

I have data with sentences of differing length that are split by rows. In my attached example I want to concatenate all [value] rows where P is followed by another P. Usually there is just two P values together but some data it can be as many as 25, therefore a static solution to this problem doesn't help me too much (which I found out the hard way). 

I originally thought a multirow formula could work but unless I specify row numbers it just ends up that P1+P2 followed by P2+P3, etc. rather than altogether. 

I hope that makes sense. I feel instinctively that there is a simple solution to this problem that doesn't involve writing out a 25 row formula. 

Thanks.

[Claje · Accepted solution]

Hi,

To do this I would use two tools.

The first is a Multi-Row formula tool, which we will use to create a record id that only increments when it does not find consecutive P's.

To do this, I built a formula like the following:

IF [Row-1:Name] = [Name] AND Name = 'P' THEN [Row-1:RecordID] ELSE [Row-1:RecordID]+1 ENDIF

This basically checks if the current row is a P and the prior row was also a P, and if that is true, it does not increment the recordid (the field we are creating).  Otherwise, it increments.

Then, we can use a summarize tool and Group by Record ID and Name, concatenating Value.  In the summarize tool when we click on the "concat_value" column, at the bottom of the tool it lets you define a separator.  If you delete this it will concatenate without a delimiter, basically the same as P1+P2+P3.

Hope this helps!

[estherb47]

Hi @jamesgough ,

If I'm thinking this through correctly, you can use a filter to isolate the P records, and a summarize tool to concatenate them together.
 Union back with the original data, and you have your concatenated fields.




Cheers!

Esther

[jamesgough]

@Claje That is so helpful, thank you! This had crossed my mind as an option but I don't have enough experience with the transpose tool. Such a big help in getting me on my way with my workflow. Really appreciate it. 

[jamesgough]

@estherb47 

Thanks for the solution. Unfortunately, not all the P values in each ID field can be grouped and then concatenated. They have to be sequential so a new ID field was needed as suggested by @Claje 

[estherb47]

@jamesgough no worries! I didn't understand the entire problem. @Claje 's solution is a great one!

Here's another solution that uses two multi-row formula tools. The first one concatenates consecutive Ps together.



The second one determines which rows to keep.

Then filter to keep the "N" only.