Group Rows

I have a workflow that outputs rows where all the fields are the same but for three columns. Network ID, Network Eff Date and Network Name

I want to only have one row output, where each network id, network eff date and Network Name are on the same row.

See Example attached.

What would be the best way to do this?

Capture.PNG

Output

Accepted answers

alexnajm

Something like this: Solved: Multi Row ID Grouping - Alteryx Community

All comments

alexnajm

Definitely possible! You'll need to add a Record ID, Transpose the data that needs 1st 2nd 3rd etc., a Formula to label each of the instances, and a CrossTab to bring it back

Play around with it as you need for your use case! For example, you may need a grouped Record ID via a Multi Row Formula rather than a Record ID tool

Group Rows.yxmd

cowannbell

Confused on how this would work. What does adding a record Id do?

alexnajm

In this case, it’s used to be able to name the 1st, 2nd, 3rd column names - do you see it in the workflow attached above?

cowannbell

Hum. Didn't see that workflow yesterday.

I am having an issue with the formula. I'm getting unknown variable with Name2. What am I missing? Where is Name2 coming from, is it just the name of a new column and if so, why the below error.

Thanks

alexnajm

It is coming from [Name] being a column in your dataset and the Transpose tool creating a [Name] column by default - because there can't be two columns called [Name], it renames the second one to be [Name2]

You'll see this behavior in the provided workflow - if you've tried implementing this in your workflow, you must've gotten rid of the [Name] column before

cowannbell

Okay, so yes. My actual table is more complicated, didn't think that would make a difference.

These are the actual fields in the table.

Product Code

TIN

Last Name

First Name

Middle Name

Address #

Address Line 1

Address Line 2

City

State

Zip

County

Phone

Net ID

Eff Date

Network Name

Net Id, Eff Date and Network Name are the variables.

alexnajm

@cowannbell In this case, you would just need to change [Name2] in the expression to [Name].

otherwise, this workflow should work well as a foundation and you can adjust as needed!

cowannbell

So, that is what I thought but it's not working.

My file has 1,264 rows. At least half of those are duplicates except for the Net ID, Net Eff Date and Network Name.

It's creating a large number of columns. It starts with 1 and goes to 1,264. The formula is not correct. It keeps counting instead of starting the count over for each group.

ToString([RecordID])+"_"+[Name]

We can't use the recodID in this case.

alexnajm

Correct, this was mentioned in my first reply. Because of the data you provided, a Record ID worked well - in this case a grouped Record ID via a Multi Row Formula rather than a Record ID tool should work well

cowannbell

I have never used the multirow formula tool before. I get the grouping but how do you get a record id this way?

alexnajm

Something like this: Solved: Multi Row ID Grouping - Alteryx Community

cowannbell

I went with the tile method instead of record id. I did use the rest of the process though.

Thank you!

alexnajm

Definitely another valid way - nicely done! If the question then is complete, go ahead and mark one of the responses as a solution. Thanks in advance!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703