Separate column into different outputs

[ssalkova]

Hello,

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

POLE_ID	CIRCUIT1	SYSTEM_VOLTAGE
27399706	B-109-J	4.16
27399706	B-14-J	4.16
27399707	B-109-J	4.16
27399707	B-14-J	4.16
27399708	B-109-J	4.16
27399708	B-14-J	4.16
27399710	B-109-J	4.16
27399710	B-15-J	4.16
27399715	B-14-J	4.16
27399716	B-109-J	4.16
27399716	B-14-J	4.16
27399716	B-15-J	4.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_ID	CIRCUIT1	CIRCUIT2	CIRCUIT3	SYSTEM_VOLTAGE
27399706	B-109-J	B-14-J		4.16
27399707	B-109-J	B-14-J		4.16
27399708	B-109-J	B-14-J		4.16
27399716	B-109-J	B-14-J	B-15-J	4.16

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

Thank You.

[Thableaus]

 Hi @ssalkova 

See workflow appended.

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

- Tile Tool to group the ID in sequences.

- Formula Tool to create Circuit + sequeence number column

- Cross-Tab Tool to put in order.

Cheers,

[ssalkova]

Hi @Thableaus

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?

[Thableaus · Accepted solution]

@ssalkova 

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

Cheers,

[ssalkova]

@Thableaus

Works perfectly! Thanks a lot for the help.

[ssalkova]

Hi @Thableaus

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

POLE_ID	CIRCUIT1	SYSTEM_VOLTAGE
27399706	B-109-J	4.16
27399706	B-14-J	4.16
27399707	B-109-J	4.16
27399707	B-14-J	4.16
27399708	B-109-J	4.16
27399708	B-14-J	4.16
27399710	B-109-J	4.16
27399710	B-15-J	4.16
27399715	B-14-J	4.16
27399716	B-109-J	4.16
27399716	B-14-J	4.16
27399716	B-14-J	2.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_ID	CIRCUIT1	SYSTEM_VOLTAGE_1	CIRCUIT2	SYSTEM_VOLTAGE_2	CIRCUIT3	SYSTEM_VOLTAGE_3
27399706	B-109-J	4.16	B-14-J	4.16
27399707	B-109-J	4.16	B-14-J	4.16
27399708	B-109-J	4.16	B-14-J	4.16
27399710	B-109-J	4.16	B-15-J	4.16
27399715	B-14-J	4.16
27399716	B-109-J	4.16	B-14-J	4.16	B-14-J	2.4

Regards.

[Thableaus]

Hi @ssalkova 

Yes it is.

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

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

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.

WF attached.

Cheers,