Hi everyone, I am trying to get column structure and order in one file (Input1) based on a different file (Input2). Input1 USER1USER6USER2USER4USER9535MKO111RRR765REEWNJ8222FFFUYTYRFBHU333DFDYTR45T765444FDFTRE Input2 FIELDSUSER0USER1USER2USERGUSER4USER5USER9USER7USER8USER6 My current solution is very close to achieving that task. However it doesn't respect empty columns and pushes everything to the left. USER1USER6USER2USER4USER9USER0USERGUSER5USER7USER8535MKO111RRR765 REEWNJ8222FFFUYT YRFBHU333DFDYTR 45T765444FDFTRE My expected result looks like this. USER0USER1USER2USERGUSER4USER5USER9USER7USER8USER6 535111 RRR 765 MKO REEW222 FFF UYT NJ8 YRF333 DFD YTR BHU 45T444 FDF TRE 765 If someone could take a look at my flow and give me a hint I would really appreciate it. Ideally I would like to convert the solution into a macro so it can be reused with many different column patterns. My real data has over 50 columns. Rearrange_Columns.yxmd

Column rearrangement based on a formatting file.

Hi everyone,

I am trying to get column structure and order in one file (Input1) based on a different file (Input2).

Input1

USER1	USER6	USER2	USER4	USER9
535	MKO	111	RRR	765
REEW	NJ8	222	FFF	UYT
YRF	BHU	333	DFD	YTR
45T	765	444	FDF	TRE

Input2

FIELDS

USER0

USER1

USER2

USERG

USER4

USER5

USER9

USER7

USER8

USER6

My current solution is very close to achieving that task. However it doesn't respect empty columns and pushes everything to the left.

USER1	USER6	USER2	USER4	USER9	USER0	USERG	USER5	USER7	USER8
535	MKO	111	RRR	765
REEW	NJ8	222	FFF	UYT
YRF	BHU	333	DFD	YTR
45T	765	444	FDF	TRE

My expected result looks like this.

USER0	USER1	USER2	USERG	USER4	USER5	USER9	USER7	USER8	USER6
	535	111		RRR		765			MKO
	REEW	222		FFF		UYT			NJ8
	YRF	333		DFD		YTR			BHU
	45T	444		FDF		TRE			765

If someone could take a look at my flow and give me a hint I would really appreciate it. Ideally I would like to convert the solution into a macro so it can be reused with many different column patterns.

My real data has over 50 columns.

Rearrange_Columns.yxmd

Output

Transformation

Accepted answers

DataNath

Hey @The1804, here's how I'd go about it:

1) Add a RecordID so we can Cross-Tab the [FIELDS] into the first row of data, in the desired order

2) Cross-Tab, setting the RecordID as the headers (as Alteryx orders them ascending by default)

3) Dynamic Rename to take the actual desired header i.e. the [FIELDS] value from the first row

3) Sample the first 0 rows so we're only left with the headers

4) Union this desired structure with the main data - ensure the configuration is as below to force the order etc

Workflow attached - hope this helps!

Force field order from structure input.yxmd

caltang

Here is my way, fixed:

Rearrange_Columns.yxmd

All comments

caltang

EDIT: Sorry, my bad - just realized the order was out. Just let me check.

DataNath

Hey @The1804, here's how I'd go about it:

1) Add a RecordID so we can Cross-Tab the [FIELDS] into the first row of data, in the desired order

2) Cross-Tab, setting the RecordID as the headers (as Alteryx orders them ascending by default)

3) Dynamic Rename to take the actual desired header i.e. the [FIELDS] value from the first row

3) Sample the first 0 rows so we're only left with the headers

4) Union this desired structure with the main data - ensure the configuration is as below to force the order etc

Workflow attached - hope this helps!

Force field order from structure input.yxmd

caltang

Here is my way, fixed:

Rearrange_Columns.yxmd

niklas_greilinger

One way of doing this is to change the configuration of the Union tool to Manually configure fileds. As @caltang mentioned this is a rather manual approach, but might help you too.

caltang

Although true, @The1804 has many more columns than what is shown. They will need a dynamic approach to suit their needs. Your Union changes are manually adjusted, which may not be dynamic enough for the requestor. Good for fixed use cases that you know won't change in fields.

caltang

Hi @niklas_greilinger please do not take my comment the wrong way. You should not edit your solution away, that is something that could help @The1804 down the road. If you can, please put back your solution so that it helps.

Re: Your question on doing it with the Union tool itself, your method of manually adjusting IS the way to do it. Other than that, for it to follow a pre-determined order - you will need to measure it against something that is ordered already. In this case, the requestor has done so with the 2nd Text input tool.

The1804

Thank you everyone for the responses! @DataNath it seems like the only missing link in my solution was the order in which I attached my connectors to the last Union tool. That is actually very strange as "Set a specific output order" doesn't seem to be doing much. I actually had to disconnect the tools and connect my desired column order first and the data second.

Anyways, I will use this solution on my data now. Thanks again!

caltang

Small ask, @DataNath ’s one doesn’t seem to be following your desired order of columns right?

My workflow sorts and follows the order you seek. If it helps, do you mind marking @niklas_greilinger and my workflow as accepted solutions as well since they serve your requirements @The1804 ?

DataNath

No need to open up a new thread - turning my flow into a macro requires 2 incredibly simple changes: make the Text Input tools Macro Input tools and then add a Macro Output tool after the union:

Now if I place this in a new workflow with my field order:

And some starting data (again, out of order and with not all fields present):

Output is as desired:

One thing to note: If you'll always be using [FIELDS] as the name for the mapping/order list, you can leave things as they are. However, if this column name can be something different like [FieldNames], [FieldOrder] etc, then you'll need to enable Field Mapping in this Macro Input anchor. You can do that here:

This just allows you to map [FIELDS] - that has been used to build the macro - to the equivalent in the actual dataset you're using so that instances of it can be replaced when it runs.

Macro and example workflow both attached as a packaged workflow. Hope this helps!

Field order macro demo.yxzp

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335