topic Re: Counting and then concatenating in Alteryx Designer Desktop Discussions

Counting and then concatenating

JorgeVazquez — Mon, 24 Oct 2016 14:53:24 GMT

Hi all,

Hope someone can help me with the following.

I have this type of outcome:

GN0030 | GN0030 | GN0030 | GN0030 | GN0030 | GN0030 | XX51500 | XX51500

CB0000 | DP0063 | MN0000 | XHT | XHT | XHT | XHT | XX51500 | XX51500

CB0000 | CB0000 | CB0000 | GN0125 | XX51500 | XX51500

I want to count and concatenate back the result so the lines would look like this:

6 x GN0030 | 2 x XX51500

1 x CB0000 | 1 x DP0063 | 1 x MN0000 | 4 x XHT | 2 x XX51500

3 x CB0000 | 1 x GN0125 | 2 x XX51500

Any ideas on how can I get this done?

Thanks!

Re: Counting and then concatenating

Federica_FF — Mon, 24 Oct 2016 15:19:13 GMT

Hi!

What is exactly the pipe/vertical line in your input and what is your input?

It's an excel file and the vertical line represent where a new column start?

Or you have a singlestring with value separated by space|space?

A way could be to use a record ID to give a unique ID to each record/line and then split to rows each value (or transpose if it's an excel file, it depends on your input) so that you have 1 value per row. Then you can easly summarize the values groupng by record ID and counting. With a formula tool is then possible to concatenate values and count adding all the texts you need and then cross tab the final result.

See the example attached

Re: Counting and then concatenating

RodL — Mon, 24 Oct 2016 15:19:16 GMT

Assuming that the lines and order are significant, you could first assign a Record ID to each line.

Then...

Text to Columns (Split to Rows) with Pipe Delimiter
Summarize grouping by RecordID and your identifier with a count.
A Formula to concatenate the count, then an "x", and then your identifier.
A Summarize tool grouped by the RecordID set to concatenate with a pipe delimiter.

That should get you close...

Re: Counting and then concatenating

JorgeVazquez — Mon, 24 Oct 2016 15:41:08 GMT

Hi RodL,

Thank you for your answer.

This was very helpful.

Thanks!

Re: Counting and then concatenating

JorgeVazquez — Mon, 24 Oct 2016 15:43:32 GMT

Hola Federica,

Thank you so much for this.

I got it! Your model helped me to solve my query.

The pipes where just spaces in my data. Everything is in one column but I have that information in separate rows and with unique IDs, so I could follow your example and get it done!

Thank you for taking the time to answer.