Hello,
I am trying to build a workflow that will compare the previous years score and subtract the difference. However, I only want to compare scores from 2 years, ignoring any nulls and if only 1 score, resulting in "not compatible".
Input
ID | 2019 | 2020 | 2021 |
Cust 1 | 4 | 2 | |
Cust 2 | 4 | 4 | |
Cust 3 | 1 | 2 | 3 |
Cust 4 | 3 | 4 | |
Cust 5 | 3 |
Output
ID | 2019 | 2020 | 2021 | Comparison |
Cust 1 | 4 | 2 | -2 | |
Cust 2 | 4 | 4 | 0 | |
Cust 3 | 1 | 2 | 3 | 1 |
Cust 4 | 3 | 4 | 1 | |
Cust 5 | 3 | Not compatible |
For example;
Cust 1 = [2020]-[2021]
Cust 2 = Because of Null in [2021], result should be [2019]-[2020]
Cust 3 = [2020]-[2021] ignoring result from [2019]
Cust 4 = Because of Null in [2020], result should be [2019]-[2021]
Cust 5 = Because of Null in [2019] & [2020], Not compatible
I would be grateful for any advice, or please reach out if you need me to explain it further.
Thank you!
Solved! Go to Solution.
Hello @Ben_S
Here, I would say one of the key tools you'll need is the Transpose tool. The years headers will then become one column and all of the corresponding number values will be another field. From here, you can filter out the null values, select only the last two records per year, and complete the subtraction you're looking for. Finally, only keep the record with the comparison then join back to the original data. Lastly, you have to use a Multi-Field tool to add "Not Compatible" because you must also change this field to a string.
Hope this helps!
Thank you so much!