Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Using Mulit-Field Formula tool to multiply only certain columns

Alastair
7 - Meteor

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 

2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Alastair
7 - Meteor

@MarqueeCrew

 

Brilliant thanks so much this worked perfectly!

Labels