community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Great than Less than Function

Asteroid

I'm writing my first Function.

I want to create a field called Facility based on a range value from the Account number field.

The account numbers are in ranges by the Facility.

 

I'm getting an error.  I think it due to how I've syntaxed by logic.  I'd appreciate some help.

MALFORMED IF STATEMENT

PARSE ERROR AT CHAR(27):

 

Here's my function code:::

 

 

IF [Visit ID] >= 100000000 AND <= 199000000
THEN "GSM"
ELSEIF [Visit ID]>= 200000000 AND <= 299000000
THEN "LMC"
ELSEIF [Visit ID]>= 300000000 AND <= 399000000
THEN "SJD"
ELSEIF [Visit ID]>= 400000000 AND <= 499000000
THEN "SVB"
ELSEIF [Visit ID]>= 500000000 AND <= 549000000
THEN "HRH"
ELSEIF [Visit ID]>= 550000000 AND <= 649000000
THEN "SJB"
ELSEIF [Visit ID]>= 650000000 AND <= 799000000
THEN "SFT"
ELSEIF [Visit ID]>= 800000000 AND <= 899000000
THEN "SMG"
ELSE "Error"
ENDIF

Alteryx Certified Partner
Alteryx Certified Partner

F [Visit ID] >= 100000000 AND [Visit ID]<= 199000000 THEN "GSM" ELSEIF [Visit ID]>= 200000000 AND [Visit ID]<= 299000000 THEN "LMC" ELSEIF [Visit ID]>= 300000000 AND [Visit ID]<= 399000000 THEN "SJD" ELSEIF [Visit ID]>= 400000000 AND [Visit ID]<= 499000000 THEN "SVB" ELSEIF [Visit ID]>= 500000000 AND [Visit ID]<= 549000000 THEN "HRH" ELSEIF [Visit ID]>= 550000000 AND [Visit ID]<= 649000000 THEN "SJB" ELSEIF [Visit ID]>= 650000000 AND [Visit ID]<= 799000000 THEN "SFT" ELSEIF [Visit ID]>= 800000000 AND [Visit ID]<= 899000000 THEN "SMG" ELSE "Error" ENDIF

 

happy new year,

 

mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted

As you are learning the formula tool, might be worth looking at the switch function as an alternative.

 

Assuming all values from x00000000 to x99999999, you could do something like 

SWITCH(FLOOR([Field1]/100000000),"Error",
	1, "GSM", 
	2, "LMC",
...
)

If you need to reject x99000000 to x99999999 then you could do that as a conditional first, e.g.:

 

IF MOD([Field1],100000000) > 99000000 
THEN "Error"
ELSE
	SWITCH(FLOOR([Field1]/100000000),"Error",
		1, "GSM", 
		2, "LMC",
...) ENDIF
Labels