Columns rearrangement post Join tool

[omkarshinde]

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

[omkarshinde]

@binuacs 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

[binuacs]

@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? 

[binuacs]

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

[binuacs]

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 .

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 · Accepted solution]

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.