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 |
Solved! Go to Solution.
if [Mode] = 'TL' and [Carrier Code] = 'FLET' then 'FLEET' ELSE [Mode] ENDIF
@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
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
@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
@ba13891 It could dislike using an = sign on a string datatype. In which case use the Contains() function to achieve same result.
@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
@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.
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.
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