Comparing Multiple Columns with Multiple Variables/Conditions

Question

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

ID201920202021Cust 1 42Cust 244 Cust 3123Cust 43 4Cust 5  3

Output

ID201920202021ComparisonCust 1 42-2Cust 244 0Cust 31231Cust 43 41Cust 5  3Not 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!

Kenda · Accepted Answer

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!