Ordering Column/Field based on a template table

[Harley]

I want a way to be able to order input tables based on a template table. The input tables could have different fields but always a subset of the template table. 

For example, my template table is like the following, which since it's a template, there is no data rows

Name

ID

Categories

Gender

Age

Grade

Date of Birth

Location

Field 1

My input tables can be dynamic in terms of field names and ordering, e.g.

Categories	ID	Location	Age
A	2	Area 1	20
B	3	Area 2	20

The columns above can be different and ordered in different ways, BUT always will be a subset of the template table.

I want a tool to order it in the order of the master template

ID	Categories	Age	Location
2	A	20	Area 1
3	B	20	Area 2

[jacob_kahn]

This is a simple task @Harley 

You should have no problem accomplishing this.

I would use a template in an Text Input Tool and configure the Union tool to output it first. I would connect it to the Union tool first just to be sure.

Please see the attached workflow and like and accept if this helps.

J

[ChrisTX]

Here's a related Idea, if you would like to add an upvote:

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Add-List-Based-Mode-to-Dynamic-Select/idi-p/...

The link includes a macro to select a subset of columns using an input list.

Here are a few more related posts:

================

The solution uses a CReW macro, Block All Records.

http://www.chaosreignswithin.com/2019/01/block-all-records.html

You have an input dataset and want to make sure that the output of your process (e.g. macro) contains all of the fields in the same order as the original file. You could use the Block All Records macro and stream that into a UNION tool. Then set the UNION to read #1 first.

================

Revert columns to incoming column order

Note: the post below is for a different topic, but includes an unusual method to return the final column list back to the same column order as the input file:

1) From the original input file: Use the tool Select Records with Range = 0. This will give you a blank data file with columns in the same order as the original input file

2) Use a Union tool with these inputs: the file from #1 above, and your processed data set, which has columns in the wrong column order. Use Auto Config by Name. This will keep the data from your processed data set, but the Union will move the columns around to match the column order from #1 above.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Developing-a-Macro-for-filling-down-va...

================

Chris

[ChrisTX]

The suggestion from @jacob_kahn may be the easiest, but you might need to use a Select tool to ensure your data types are the same in both streams.

And in the Union tool, select the option for Output Commons Subset of Fields

Chris

[Harley · Accepted solution]

@jacob_kahn Thanks for your reply. Doing a bit more digging, I figured it out. 

The solution you provide only works if the template is the #1 input. And this is the key. 

I was confused originally as I thought if you set the Output Order it will do the job.

[jacob_kahn]

@Harley 

Exactly why I suggest to place it first!

Hope it helped 🙂 

J