Hi,
A record ID changed. Year 1 depicts the sales using the old ID. Subsequent years show sales using the new ID.
I want to move the sales for Year 1 to the new ID. Sometimes the switch occurs after Year 2How can I do that?
Any ideas? I assume a Multi Row Formula tool, but I am a bit stuck.
ID | Year1 | Year2 | Year3 | Year4 | |
A--Old ID | 0604120 | 34 | 0 | 0 | 0 |
A--New Id | 1206120 | 0 | 42 | 41 | 46 |
A-Desired | 1206120 | 34 | 42 | 41 | 46 |
B--Old Id | 0303142 | 123 | 166 | 0 | 0 |
B--New ID | 1203142 | 0 | 0 | 175 | 225 |
B-Desired | 1203142 | 123 | 166 | 175 | 225 |
Solved! Go to Solution.
I assume you have some sort of look up table or a way to link new and old IDs? If so, use that to assign the same ID to all records of the same customer. If you have a look up table, the Find Replace tool will be handy.
Once you have a common ID system assigned, a Summarize tool can be used to group by that common ID and find the sum of each year field. I have attached an example workflow.
I have not come across that tool. But, it might be my new best friend. Thank you .