Column rearrangement based on a formatting file.

[The1804]

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.

[caltang]

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

[DataNath · Accepted solution]

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!

[caltang · Accepted solution]

Here is my way, fixed:

[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 ?

[The1804]

Hi,

They both achieve exactly the same result. While the 1st solution is much simpler.

Of course I can also mark your solution as accepted as it also produces expected data.

Do you think it's possible to easily remake it into a macro so it can be used in multiple flows? I currently have around 65 flows which need this feature implemented and I don't really want to copy all the blocks to each of them. If no I will make a new thread regarding this question.