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.
Solved! Go to 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
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.
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 |