Columns rearrangement post Join tool

I have my left and right data column anchors of Join tool arranged in the desired order as above. However after join I wish to arrange them one after another without disturbing this order as shown below:

How can I achieve this? Help appreciated!
Note: This is just a mock data above and my real data contains over 100+ columns

Workflow

Datasets

Input

Help

Dynamic Processing

Join

Preparation

Accepted answers

flying008

Hi, @omkarshinde

1- Cross Tab tool to generate complete multi column calculation fields in order.

2- Multi Row Formula tool to generates corresponding formulas for calculation based on field types.
3- Dynamic Replace tool to calculate the results of the comparison column according to the formula above,
4- Dynamic Rename tool to restore the original field column names.

All comments

OTrieger

@omkarshinde
The Join tool enable you to arrange the order of the fields, just move them to the desired order

binu_acs

@omkarshinde one way of doing this

Field_reOrder_17102024.yxzp

omkarshinde

Hey @binu_acs Appreciate your help!
However the macro is messing up the column field names when there are more than one words by replacing spaces with additional _, replacing / with _ and so on because of which union tool is considering all column names as new

omkarshinde

Hey @OTrieger there are actually 100 fields+ and reordering 200 fields inside join tool is becoming a hassle

binu_acs

@omkarshinde Can you provide the sample fields name which you are acing issue? i will adjust the regex formula according to that

omkarshinde

@binu_acs example whenever there are:-
multiple words in column name separated with spaces then they are replaced as _ instead
spl chars in column names such as () + / - then they are replaced as _ instead

binu_acs

@omkarshinde @That is the default property of the cross tab tool, it will convert any special character to _, if you have special characters in the field name, then I need to make some adjustments in the macro, one question, is both input files are having same number of fields or it may differ

omkarshinde

@binu_acs both input files have exact same name and fields number

binu_acs

@omkarshinde updated workflow attached

Field_reOrder_17102024.yxzp

omkarshinde

@binu_acs Thanks a ton. Any way I can insert comparison columns adjacent to each of these pairs now?
Example-
ID column Right_ID column Comparison column

binu_acs

@omkarshinde If you have 100 columns then are you planning to create 50 comparison fields and re-order dynamically? If yes then that also possible, one thing is that you need to make sure that the comparison column ends with the actual column name eg: ID, Right_ID, Comparison_ID

omkarshinde

yes if there are 100 fields I am looking to add 50 flag columns for them right beside each pair. Can this be done via multi field tool? Or i need to make changes in your macro?

binu_acs

Macro is to re-order the fields, what kind of comparison are you planning to implement on each set of fields, are you excepting the comparison fields are also created dynamically or you will create manually using formula tool?

omkarshinde

for string fields i would like to compare if lD is present in Right ID or no
and for numeric fields if Right ID >= ID or no
I am looking to make this dynamic if possible instead of having to create 50 comparison columns manually

binu_acs

that is going to be a lots of work needs to be done in the workflow, because dynamically pick the fields based on the data type and create another field is bit tricky

flying008

Hi, @omkarshinde

An Full Dynamic solution for automatic join and add compare multiple fields for calculation. FYI .

录制_2024_10_19_09_44_39_871.gif

ps: I can not upload any .yxmd file to here, so please try it yourself with the above workflow and gif.

	Left Input						Right Input
ID	State	Loc	Category	Job		ID	State	Loc	Category	Job
1	2	3	ab	5		1.5	2.5	3.5	abc	5.5
1	2	3	4	5

	Output
ID	Right_ID	Comparison ID	State	Right_State	Comparison State	Loc	Right_Loc	Comparison Loc	Category	Right_Category	Comparison Category	Job	Right_Job	Comparison Job
1	1.5	-1	2	2.5	-1	3	3.5	-1	ab	abc	-1	5	5.5	-1
1		0	2		0	3		0	4		0	5		0

omkarshinde

@flying008
Thanks for the help! Much appreciated! Grateful!
Can you expand please on the cross tab, multi row formula tool, Dynamic replaces, dynamic renames logics?

flying008

Hi, @omkarshinde

1- Cross Tab tool to generate complete multi column calculation fields in order.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317