Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Ordering Column/Field based on a template table

Harley
7 - Meteor

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

CategoriesGenderAgeGradeDate of BirthLocationField 1

 

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

CategoriesIDLocationAge
A2Area 120
B3Area 220

 

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

IDCategoriesAgeLocation
2A20Area 1
3B20Area 2

 

 

5 REPLIES 5
jacob_kahn
12 - Quasar

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.

 

the_jake_tool_0-1592570013383.png

 

the_jake_tool_1-1592570027059.png

 

 

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

 

J

ChrisTX
15 - Aurora

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
15 - Aurora

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

 

ChrisTX_0-1592573489788.png

 

 

Chris

Harley
7 - Meteor

@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.

 

Harley_0-1592573474763.png

 

jacob_kahn
12 - Quasar

@Harley 

 

Exactly why I suggest to place it first!

 

Hope it helped 🙂 

 

J

Labels