Alteryx Formula Help IF AND THEN ELSE

[ba13891]

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

[MilindG]

@ba13891 

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

[StellaBon · Accepted solution]

@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