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

Great than Less than Function

brendafos
10 - Fireball

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

2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
jdunkerley79
ACE Emeritus
ACE Emeritus

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