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.
Solved! Go to 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!
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
@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.
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
@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.