Alteryx Formula Help IF AND THEN ELSE

Hello,

I would like to create a new output field as seen in Mode 2 below. I created a formula using the following SQL: IF [MODE] = "TL" AND [CARRIER CODE] = "FLET" THEN [new field (mode 2)] = "FLEET" ELSE [MODE] END IF

I received a syntax error when trying the above SQL. Any thoughts on how I can accomplish this?

Mode	Carrier Code	Mode 2 (new output field)
TL	LOGI	TL
TL	FLET	FLEET
TL	LOGI	TL
TL	LOGN	TL

Best Practices

Accepted answers

StellaBon

@ba13891 It would look like this in Alteryx:

(First select create new column and name it.)

IF [Mode] = "TL"

AND [Carrier Code] = "FLET"

THEN "FLEET"

ELSE [Mode]

ENDIF

Case has to match, ENDIF is all one word. Hope this helps.

StellaBon

All comments

MilindG

@ba13891

if [Mode] = 'TL' and [Carrier Code] = 'FLET' then 'FLEET' ELSE [Mode] ENDIF

Fleet.yxmd

StellaBon

@ba13891 It would look like this in Alteryx:

(First select create new column and name it.)

IF [Mode] = "TL"

AND [Carrier Code] = "FLET"

THEN "FLEET"

ELSE [Mode]

ENDIF

Case has to match, ENDIF is all one word. Hope this helps.

StellaBon

ba13891

Thanks for the feedback!

I used the attached SQL, which is actual field names. I receive a syntax error at or around '='.

Do I need to consider anything being that I'm doing an "in database formula"? Any suggestions on resolving the error based on what you see below?

IF `rtrim(MODE_STD)` = "TL"
AND `DOC_TYP_CD` = "FLET"
THEN "FLEET"
ELSE `rtrim(MODE_STD)`
END IF

ba13891

@StellaBon sorry to ask this again, but thought I should direct it towards you

Do you know why I would get a Parse Syntax Error aroudn the = of the statement below?

IF `rtrim(MODE_STD)` = "TL"
AND `DOC_TYP_CD` = "FLET"
THEN "FLEET"
ELSE `rtrim(MODE_STD)`
ENDIF

Thanks,

ba13891

StellaBon

@ba13891 It could dislike using an = sign on a string datatype. In which case use the Contains() function to achieve same result.

ba13891

@StellaBon I tried the statement below, and received a new syntax error at or near '`rtrim(MODE_STD)`': extra input '`rtrim(MODE_STD)`'. (Line 1, pos 530)

IF Contains `rtrim(MODE_STD)`,"TL"
AND contains `DOC_TYP_CD`,"FLET"
THEN "FLEET"
ELSE `rtrim(MODE_STD)`
ENDIF

StellaBon

@ba13891 Just saw that you are using In-Database. You have to use the syntax that works with your type of SQL. Because it is a hybrid of what your SQL type is and what Alteryx accepts, if your sql syntax isn't working you may need to google a variety of ways to write it in your native syntax until you hit on one that Alteryx accepts. I am guessing it will be closer to

Case WHEN

"Column1" LIKE("FLET")

AND "COLUMN2" LIKE("abc")

THEN "FLEET"

ELSE NULL

END

But do google this for exact structure.

ba13891

Thank you @StellaBon. Native syntax may be a little above my expertise; however, I did shift to the basic formula tool, which led to succes via your previous advice.

Rana_Kareem

Hi @ba13891 ..

Yes, as StellaBon said

You have to use CASE Statement instead of IF Statement.

Also, I see you are using rtrim function, you should put it outside the backticks. ``

The expression will be:

CASE
WHEN rtrim(`MODE_STD`) = "TL"
AND `DOC_TYP_CD` = "FLET"
THEN "FLEET"
ELSE rtrim(`MODE_STD`)
END

Hope that helps

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335