Hello All,
I need some help with changing values depending on the given condition below.
I've made a new "Code" column where Name A = Code 1, Name B = Code 2, etc. with the following formula:
IF [Name] = "A" then "1"
elseif [Name] = "B" then "2"
elseif [Name] = "C" then "3"
elseif [Name] = "D" then "4"
elseif [Name] = "E" then "5"
elseif [Name] = "F" then "6"
elseif [Name] = "G" then "7"
else "0" endif
This repeats on a monthly basis:
| Date | Name | Amount | Code |
| 2021-07-31 00:00:00 | A | 100 | 1 |
| 2021-07-31 00:00:00 | B | 100 | 2 |
| 2021-07-31 00:00:00 | C | 0 | 3 |
| 2021-07-31 00:00:00 | D | 100 | 4 |
| 2021-07-31 00:00:00 | E | 100 | 5 |
| 2021-07-31 00:00:00 | F | 100 | 6 |
| 2021-07-31 00:00:00 | G | 100 | 7 |
| 2021-08-31 00:00:00 | A | 100 | 1 |
| 2021-08-31 00:00:00 | B | 100 | 2 |
| 2021-08-31 00:00:00 | C | 100 | 3 |
| 2021-08-31 00:00:00 | D | 100 | 4 |
| 2021-08-31 00:00:00 | E | 100 | 5 |
| 2021-08-31 00:00:00 | F | 100 | 6 |
| 2021-08-31 00:00:00 | G | 100 | 7 |
However, now I have a new condition where if Name "C" has Amount "0" then the Code for Name "A" needs to be "11". This also needs to take the dates into consideration as some monthly dates may have Name "C" with an Amount greater than 0 and can remain with the same Code "1".
Essentially, I need to get the following output:
| Date | Name | Amount | Code |
| 2021-07-31 00:00:00 | A | 100 | 11 |
| 2021-07-31 00:00:00 | B | 100 | 2 |
| 2021-07-31 00:00:00 | C | 0 | 3 |
| 2021-07-31 00:00:00 | D | 100 | 4 |
| 2021-07-31 00:00:00 | E | 100 | 5 |
| 2021-07-31 00:00:00 | F | 100 | 6 |
| 2021-07-31 00:00:00 | G | 100 | 7 |
| 2021-08-31 00:00:00 | A | 100 | 1 |
| 2021-08-31 00:00:00 | B | 100 | 2 |
| 2021-08-31 00:00:00 | C | 100 | 3 |
| 2021-08-31 00:00:00 | D | 100 | 4 |
| 2021-08-31 00:00:00 | E | 100 | 5 |
| 2021-08-31 00:00:00 | F | 100 | 6 |
| 2021-08-31 00:00:00 | G | 100 | 7 |
Please let me know how I can solve this!
