Hi
I have the following data set:
Transaction ID | Product ID | Value | Date Paid | Years |
T1 | 00001 | £487,694.00 | 12/01/2017 | 1 |
T2 | 00002 | £19,909.00 | 12/10/2017 | 0 |
T3 | 00003 | £250,656.00 | 17/06/2016 | 1 |
T4 | 00004 | £144,386.00 | 03/11/2017 | 0 |
T5 | 00005 | £430,619.00 | 30/04/2017 | 1 |
T6 | 00006 | £147,934.00 | 17/11/2017 | 0 |
T7 | 00007 | £359,626.00 | 31/05/2017 | 1 |
T8 | 00008 | £353,061.00 | 11/06/2017 | 0 |
T9 | 00009 | £148,527.00 | 20/10/2017 | 0 |
T10 | 00010 | £493,233.00 | 18/08/2017 | 0 |
T11 | 00011 | £171,198.00 | 12/04/2017 | 1 |
T12 | 00012 | £11,512.00 | 19/11/2016 | 1 |
If in the years column there is a 1 i need to create a new row below it, copy the Product ID, add a %increase to the value and add 1 year to the date in that newly copied row.
Can anyone suggest the best way to do this. (there are 2500 rows - above is a small sample)
Thanks
Solved! Go to Solution.
@MattDuncan See if the attached workflow accomplishes what you're looking for.
It is a little messy just because the monetary values have to be converted to numbers without symbols, and the Date Paid has to be converted to a date (not string) field. The main key, though, was to use the Formula tool to concatenate two values in one field (the original Value and the Value times 1.07). You can change that percentage to whatever you need. Then, I used a Text to Columns tool to split it into rows and a Multi Row Formula tool to add a year to the date. Lastly, you can add the symbols back in to the Value column and change the Date Paid column back to the format you were using before if need be.
Hope this helps!
Hi Matt Duncan,
I understand your request is to have an additional row but would appended columns be sufficient? For instance, "Adjusted Year" and "Adjusted Value".
If It is I would use the multi row formula tool.
Select Create New Field. Insert Name of field in this case "Adjusted Value" for the example.
Example formula: IF [years] == '1' THEN [Row+0:Value]*1.25 ELSE [Row+0:Value] ENDIF
This is saying if years is 1 return adjusted value otherwise return your original value.
You could do the same for years.
Let me know if this is sufficient for your needs. It stops you from having multiple entries for your transaction IDs/Product IDs. If I was working on a data set I would prefer single entries so this is entirely based on my preference and not your original request which I can't think of how to address off the top of my head. . .
Kind regards,
Cameron