Dynamic auto arranging in join tool

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.

Sample1.xlsx

Sample2.xlsx

Workflow

Output

Help

Dynamic Processing

Join

Preparation

Accepted answers

DavidP

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.

Preserve field order after join.yxmd

All comments

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?

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

Hi @adarsh2608

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

Preserve field order after join.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167