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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Separate column into different outputs

Atom

Hello,

 

My initial data looks like this. The Pole_ID's are showing as duplicates because some Pole_ID's have multiple Circuit1: 

POLE_IDCIRCUIT1SYSTEM_VOLTAGE
27399706B-109-J4.16
27399706B-14-J4.16
27399707B-109-J4.16
27399707B-14-J4.16
27399708B-109-J4.16
27399708B-14-J4.16
27399710B-109-J4.16
27399710B-15-J4.16
27399715B-14-J4.16
27399716B-109-J4.16
27399716B-14-J4.16
27399716B-15-J4.16


The output that I would like to get is that if a Pole_ID has multiple Circuit1, then each different Circuit1 be outputted into a new column along with its associated System_Voltage.

Output example:

POLE_IDCIRCUIT1CIRCUIT2CIRCUIT3SYSTEM_VOLTAGE
27399706 B-109-JB-14-J 4.16
27399707 B-109-JB-14-J 4.16
27399708  B-109-JB-14-J 4.16
27399716 B-109-JB-14-JB-15-J4.16

 

Can anyone help with showing me a sample workflow in Alteryx on how this could be done?

 

Thank You.

Alteryx Certified Partner

 Hi @SSalk 

 

See workflow appended.

 

EDIT: I put the circuit name in each column, correcting the Cross-Tab Tool.

 

Circuit.PNG

 

 

- Tile Tool to group the ID in sequences.

- Formula Tool to create Circuit + sequeence number column

- Cross-Tab Tool to put in order.

 

Cheers,

Atom

Hi @

 

Thank you for you quick reply, however I don't see the option to Concatenate under Method for Aggregating Values with the Cross Tab Tool. 
How can I get this Concatenate option to come up?
CrossTab.PNG

 

Alteryx Certified Partner

@SSalk 

 

Change "Values for New Columns" option to "CIRCUIT1", which is the field with your circuit names.

 

Cheers,

Atom

@

 

Works perfectly! Thanks a lot for the help.

Atom

Hi @

 

Sometimes there are cases where the same circuit will also have different voltages:

POLE_IDCIRCUIT1SYSTEM_VOLTAGE
27399706B-109-J4.16
27399706B-14-J4.16
27399707B-109-J4.16
27399707B-14-J4.16
27399708B-109-J4.16
27399708B-14-J4.16
27399710B-109-J4.16
27399710B-15-J4.16
27399715B-14-J4.16
27399716B-109-J4.16
27399716B-14-J4.16
27399716B-14-J2.4

 

Is it possible to have an output that can have separate columns: Circuit1, System_Voltage1, Circuit 2, System Voltage 2 etc. for the same Pole_ID:

POLE_IDCIRCUIT1SYSTEM_VOLTAGE_1CIRCUIT2SYSTEM_VOLTAGE_2CIRCUIT3SYSTEM_VOLTAGE_3
27399706B-109-J4.16B-14-J4.16  
27399707B-109-J4.16B-14-J4.16  
27399708B-109-J4.16B-14-J4.16  
27399710B-109-J4.16B-15-J4.16  
27399715B-14-J4.16    
27399716B-109-J4.16B-14-J4.16B-14-J2.4

 

Regards.

Alteryx Certified Partner

Hi @SSalk 

 

Yes it is.

Create SYSTEM_VOLTAGE sequence as you did with CIRCUIT in formula tool.

 

part1.PNG

 

Then, you split your Cross-Tab step in two. One for the Circuits, and the other for System Voltage.

part2.PNGpart3.PNG

 

Join by Pole_ID and bring the fields together. You might leave the "Unknown" box of the Join Tool checked as new fields can show up depending on how many circuits and voltages per Pole_ID.

 

part4.PNG

 

WF attached.

 

Cheers,

Labels