When connecting two files, how can I easily move columns together based on Column name?

[deckthehalls]

Hi everyone, 

I am in need of some help. I have two outputs, and I want to join them together and dynamically move columns next to one another based on matching column headers. In this example, the column headers are dates. Is there a way to do this with a large data set without having to use the select tool each time? 

Thanks!

[Emil_Kos]

Hi @deckthehalls,

Please disregard 🙂 I misunderstand your question.

[Emil_Kos]

Hi @deckthehalls,

I initially thought that you want to union the data.

To sort the data by columns you will need to use crew macro:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-do-I-automatically-Order-Columns/m...

For example this:

Will become this:

You need to keep in mind you can't have exactly the same columns in one file so you will need to add something to your date. Event invisible space will do it.

So for example one column needs to be named "1-jun" and the second "1-jun ".

I hope this helps. 

[mceleavey]

Hi @deckthehalls ,

Use the transpose tool, grouping by state column.

Union both together, sort by name, then crosstab, grouping by the state, using name as name and value as value.

If you want to post your data I can build it for you.

M.

[deckthehalls]

Hi @mceleavey,

Can you select more than one value using the cross tab tool? right now, I only see the option to select one value.

Thank you for your response!

[mceleavey]

Hi @deckthehalls ,

You'll need to add something to the column headers to denote the second input as you can't have two columns with the same name, obviously. 

For example, use the Dynamic Rename to add _2 to the names on the second table.

Transpose both table, union together, sort by name, crosstab grouping by state then you should have what you need. Like I said, if you post some data myself or @Emil_Kos  can build it for you.

M.

[deckthehalls]

Thank you for the explanation @mceleavey ! I was able to get to the crosstab tool, but I was having trouble getting the dates in order. I have attached an example of my data for reference. I appreciate the help 🙏

[Emil_Kos]

Hi @deckthehalls, like @mceleavey mentioned I would gladly help you but I have my phone only.

maybe you can check the crew macro. There is a working solution there that will allow you to easily sort the column order in ascending or descending order.

[deckthehalls]

@Emil_Kos @mceleavey Thank you both for responding! The only error I am running into is sorting the columns by date in chronological order. I have tried the Feild Sort tool as well, but it looks like I need to convert the dates using the DateTime tool. However, having a suffix/prefix prevents me from utilizing the DateTime tool to then put the dates in order. This is where my main issue lies now.