Counting and then concatenating

JorgeVazquez

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!

Transformation

Accepted answers

Federica_FF

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

Example.yxmd

All comments

Federica_FF

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?

See the example attached

Example.yxmd

RodL

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

JorgeVazquez

Hi RodL,

Thank you for your answer.

This was very helpful.

Thanks!

JorgeVazquez

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335