Merge Rows Data into 1 - Names

[DanielRod]

Hi Everyone,

I'm looking to merge 2 rows in order to have a "Last Name,First Name" in the same cell followed by that persons ID number and currently have a list that looks like this:

What I have

1 Smith 

2 John

3 000001

4 (Blank)

5 Smith

6 Jane

7 000002

8 (Blank)

9...

etc

What I need:

Column A                      Column B 

1 Smith,John                 000001

2 Smith,Jane                 000002

...etc

Any help you could provide would be greatly appreciated.

Thank you!

[ggruccio]

Hi @DanielRod,

There may be a way to do this with fewer steps, but here's how I would approach it.  Append a value that you can use to group on.  It looks like every four rows you have a break and then the next record, so use the multi-row formula tool to go back four rows and add +1.  Then summarize on that field and concatenate the values.  The rest is clean-up.   I've attached a workflow that does this.

See the attached workflow below:

[DanielRod]

Hi @ggruccio,

Thank you for your help with this and for building this workflow for me. Since I'm quite new to Alteryx, I have a couple questions.....

1. Why did you add 2 Text Input tools if I only have 1 document? Is there an underlying reason?

2. I should be getting about 1200 rows but when I upload the document which have around 4000, it says that the limit is 1000, and by the end, when it gets to the output tool, it only gives me 250 rows.....Is there something I should be doing to achieve a higher number of rows?

Thank you ever so much

[DanielRod]

Hi @ggruccio ,

Thank you for your help with this and for building this workflow for me. Since I'm quite new to Alteryx, I have a couple questions.....

1. Why did you add 2 Text Input tools if I only have 1 document? Is there an underlying reason?

2. I should be getting about 1200 rows but when I upload the document which have around 4000, it says that the limit is 1000, and by the end, when it gets to the output tool, it only gives me 250 rows.....Is there something I should be doing to achieve a higher number of rows?

Thank you ever so much

P.S.: Sorry for the post duplication

[ggruccio]

Hi @DanielRod,

The second text input was added simply so I could have something to build the count logic off of in the multi-row formula....something that I could build a value that shows the first four rows are all in the same group...the next four rows are all in the same group etc.  In hindsight I probably should have just used a Formula tool instead.

As to your row reduction issue....

I am not troubled that your 1000 rows goes to 250 - that appears to be a given...what we would want to do is take every four rows and make them into one.

I'm troubled though that your roughly 4000 row file is being truncated to 1000 rows.  Are you copying and pasting the data into the Text Input tool?  If so please try loading in an Input Data tool and connecting to the same logic...

Also make sure there is no Record Limit set in the Input Data tool that would be restricting the number of records.

[DanielRod]

Hi @ggruccio,

thank you for your prompt reply. So I've removed one of the Text Input tools and replaced it with an Input Data tool, and it shows all the 4593 rows I have, but once it gets to the Summarise tool, it reduces it to 256, and it shows several names within the first row for some reason...

Leaving a few Print Screens so you can see what I mean:

Thank you in advance

[ggruccio · Accepted solution]

Got it,

I've revised the logic to use the Formula tool - wondering if there was an issue with the append fields logic that worked on a relatively small dataset, but had difficulty when it got larger.

Additionally the workflow as it is built assumes that the same pattern of four rows of input data converts to one row of output data.  Is this the case for all the data, or does it vary sometimes 3 rows should make up an output row, or 5 etc?  

[DanielRod]

Amazing @ggruccio !! This worked! Just substituted the Text Input for an Input Data and that done the trick! Really appreciate your help!!

[DanielRod]

Hi @ggruccio,

is there a way to include the option to add a 0 (zero) to cells that only have 5 digits, ie.: 12345 becoming 012345

Thank you very much!