Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Dynamic auto arranging in join tool

adarsh2608
8 - Asteroid

Hello,

 

I have two excel data which need to join in one output , the problem is that the columns in the excel 1 and 2 are dynamic there will new columns will be added leaving old one so when the so the output is not in order , how to dynamically identify the new column in join tool and replace it to old position 

 

adarsh2608_1-1654166522425.png

output is the result of the year from 2013 to 2022 from one excel and jan to jun from another excel
every time year and month will be changing , it could be 2014 to 2023 and jan will become jan-feb to july so in the join tool it is adding at the end . and there is only one "unknown and dynamic " field available which is also limiting me to  arrange in order.

6 REPLIES 6
Hamder83
11 - Bolide

What do you want to join the two files on?

adarsh2608
8 - Asteroid

Hello @Hamder83 ,

 

I just need to join two excel data in order where 1st excel column should show 1st then 2nd . but the problem is that if the new column added in both excel 1 and excel 2 and we have only one column for dynamic and unknown columns

ddiesel
13 - Pulsar

Hi @adarsh2608!

 

I looked at your sample data, but the problem is unclear to me. Can you provide more details?

adarsh2608
8 - Asteroid

Hello ,

 

Excel 1 one has years from 2013 to 2022 which is dynamic because next time the data will be 2014-2023 similarly in the excel 2 it is from jan to june as in next time excel data will be feb to july . when i join the final output should start from excel from 2013-2022then jan to jun in and order 

 

but when data changes new entry is added at the end of the column and more over there is only one 'dynamic and unknown " column since i have two excel and both data changes every time

smoskowitz
12 - Quasar

@adarsh2608 --

 

There isn't anything to join on. All you can do is create for each year a full set of months and join on that, For this the Generate Rows tool might be your friend. You could for 2013 automatically have:

 

2013

January
2013February
2013March
2013April

.....etc

Seth

DavidP
17 - Castor
17 - Castor

Hi @adarsh2608 

 

Having read through all the above, I think I understand the problem. 

 

When you build your workflow and the join tool is configured, the 1st run correctly puts all your Year fields left and then the Months fields to the right. However, when you run it at a later stage, any new fields come in under *Unknown and is therefore added at the right hand side of the combined dataset, even if it's a Year field.

 

The way I would try to overcome this is to define the correct field order separate to the Join and then use a Union tool to add the Join output to my pre-ordered field names. The example below does this. I've included comments under every tool to explain what is happening. 

 

DavidP_0-1654264191367.png

 

Labels