Dynamically create new column on difference between two columns

[CCrawford94]

Hello all, 

I need to take the difference of two columns to get the difference. This needs to be dynamic because the name of the two columns that the difference are based on years and the years can change but it will always be a recent year minus a past year. I am trying to add this to a workflow so that when i run it with different year sets it will not break. 

For example I start with data like this:

2012	2013
702877	637933
596282	85726
164553	114312
1207762	1239885
257656	118491
191803	47450
143671	100259
724873	62962
1901396	1674654

and end with this:

2012	2013	Difference
702877	637933	-64944
596282	85726	-510556
164553	114312	-50241
1207762	1239885	32123
257656	118491	-139165
191803	47450	-144353
143671	100259	-43412
724873	62962	-661911
1901396	1674654	-226742

Please any help would be great.

[NicoleJohnson · Accepted solution]

Check out the attached example (version 11.8)... I used Text input to create a "Year 1" and "Year 2" column with no data, then unioned it to the data provided by position (which renames your columns from whatever year they are to "Year 1" and "Year 2"). You can then use a formula to calculate Year 2 - Year 1 to get your Difference... and then a Dynamic Rename to rename the columns with the original years (choose the Take Field Names from Right Input Metadata option in the Dynamic Rename tool).

Should work no matter what the year column names are in your original data.

Hope that helps!

Cheers,

NJ

[CharlieS · Accepted solution]

@CCrawford94

If the years are the only two columns, @NicoleJohnson created a very elegant solution. I have a different package on the same solution in case there are more columns of data besides the two years of interest.

[AnkitDwivedi6]

Hello i really likes your solution but when i am applying this in may problem i am not getting desired output. Could you please check if there is any amendment required? Here Dates are dynamic in main data sheet.

Field1	Field2	Field3	Date1(PREV DATE)	Date2(LATEST DATE)	DIFF (DESIRED COLUMN)
ABC	125L	GM12	12	-15	(-15)-12
DFG	125X	RF34	18	-16	(-16)-18
LKM	758T	RT56	82	36	36-82