Dynamic auto arranging in join tool

[adarsh2608]

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 

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.

[Hamder83]

What do you want to join the two files on?

[adarsh2608]

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]

Hi @adarsh2608!

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

[adarsh2608]

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]

@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
2013	February
2013	March
2013	April

.....etc

Seth

[DavidP · Accepted solution]

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.