Using Mulit-Field Formula tool to multiply only certain columns

[Alastair]

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 

[MarqueeCrew · Accepted solution]

@Alastair,

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

[Alastair]

@MarqueeCrew

Brilliant thanks so much this worked perfectly!