community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Addresses: 1 Row to Multiple Rows

I have a source file that includes multiple remittance addresses for a single vendor. To put it very simply, I am looking for a workflow that will do the following:

 

Vendor #LOCATION_1ADDR_1CITY_1STATE_1ZIP_1LOCATION_2ADDR_2CITY_2STATE_2ZIP_2LOCATION_3ADDR_3CITY_3STATE_3ZIP_3
V0001231PO BOX 88888DALLASTX75222-26892575 WEST MURRAY BOULEVARDATLANTAGA303053PO BOX 200715DALLASTX75320-0715

 

To

 

Vendor #LocationAddressCityStateZip
V0001231PO BOX 88888DALLASTX75222-2689
V0001232575 WEST MURRAY BOULEVARDATLANTAGA30305
V0001233PO BOX 200715DALLASTX75320-0715

 

Not all of the vendors will have 3 addresses (some may have less and some may have more) it will depend on the LOCATION_X field in the source data where X = # of locations.

 

Thanks!

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Mhynek1412 

 

Here's a way to do this:

 

Sol13.PNG

- Transpose all columns, except Vendor#

- Parse column "Name" - you will separate Columns and GroupID

- Cross-Tab to get column names where they need to be. You group by the ID field created.

- Use Select tool to deselect unnecessary columns, rename fields, or put them in order.

 

WF appended.

 

Cheers,

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@Mhynek1412,

 

Here's a dynamic approach:  Transpose the data, get rid of the underscore digits, calculate the location number on each row, get rid of the location (original) field, Cross Tab your data.

Capture.PNG

This will get you your desired results.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels