Good day Community,
I have 144 fields, named "period 1" ....... "period 144"
I want to multiply periods 13 - 24 by a X then periods 25 - 36 by Y and so on and so on.
Is there a a way to do this using 1 multi-formula tool? I can do it with 12 tools but want to reduce tool usage.
Thanks in advance
Solved! Go to Solution.
Wow! It's been at least 3 hours since I checked into the community.
IF ToNumber(replace([_CurrentFieldName_],"Period ",'')) >= 1 && ToNumber(replace([_CurrentFieldName_],"Period ",'')) <= 12 THEN [_CurrentField_] * 1 ELSEIF ToNumber(replace([_CurrentFieldName_],"Period ",'')) <= 24 THEN [_CurrentField_] * 2 ELSEIF ToNumber(replace([_CurrentFieldName_],"Period ",'')) <= 36 THEN [_CurrentField_] * 3 ELSEIF ToNumber(replace([_CurrentFieldName_],"Period ",'')) <= 48 THEN [_CurrentField_] * 4 ELSE 0 ENDIF
Here's a sample configuration for your Multi-Field Formula tool. In the first argument I account for periods 1-12. Thereafter we know that the period is greater than 12. The next ELSEIF statements just need to test for the high range. In my sample formula I multiply by a constant. Feel free to multiply by a variable. The field name is "PERIOD 999" where we get replace PERIOD(space) with nothing. That leaves us with a number which we format to a number for ease in the comparison.
Regex_Replace([_CurrentFieldName_],"[^0-9]",'')
will do this without regard to the names. Anything that isn't a number (0-9) will get zapped.
Cheers,
Mark