community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Concatenating unknown number of subsequent rows

Meteor

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.

Magnetar
Magnetar

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!

 

Pulsar

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.


image.png

Cheers!

Esther

Meteor

@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. 

Meteor

@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 

Highlighted
Pulsar

@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.
image.png


The second one determines which rows to keep.

image.png

 

Then filter to keep the "N" only.

 

Labels