Group Rows

[cowannbell]

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?

[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

[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

Pin

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