Formula (conditional statement) - Trying to recreate a SQL case statement

[jmorris14]

Hello,

I am new to Alteryx. I am trying to recreate a formula that I built in SQL within Alteryx and it is proving to be quite difficult. Here is what I have built;

IF [Contract_Status]= "Accepted"

AND [product_id]= 153 OR [product_id]=155 OR [product_id]=157
AND [date_paid]>=2017-08-01 AND [date_paid]<=2017-12-31
THEN "Non-Renewal"
ELSE "Other"
ENDIF

When I run it I get the error "The field " " is not contained in the record. (Expression #1)"

Essentially what I am trying to say is if contract_status=accepted AND if product_id CONTAINS either  153 or 155 or 157 AND the date_paid falls in that range then call is "Non-Renewal"

What do I need to do differently?

[danrh · Accepted solution]

All you need to do is give your field a name.  Click on the "Select Column" dropdown, select "+ Add Column" and type in the name of the calculated field.  This should take care of your error for you.

On a side note, I'd advise changing the or's to an in statement or wrapping them with parentheses to make sure the logic is what you're intending.

Welcome to Alteryx!

[jmorris14]

Awesome! Thanks!

[jmorris14]

That was really helpful. However, I am running into a few more issues. What I was trying to accomplish was use the formula in replacement of a Case statement inside a select statement (see below). This has really be challenging. It is doing a one to many join in alteryx whereas in sql the case statement acted more as a filter within a select statement which eliminated the one to many issue. 

Here is the complex query I am trying to recreate:

SELECT organization.id
, organization.name
, organization.attendance_weekly
, organization.date_next_renewal
, organization.date_official_start
, (CASE

WHEN (part 1 formula) 
THEN "Non-Renewal"

WHEN (part 2 formula)
THEN "Renewal"

WHEN (part 3 formula)
THEN 'New Church in 2016/2017'

END) AS "renewal_status" 

FROM organization 
WHERE date_next_renewal >='2017-01-01' AND date_next_renewal <='2018-12-31' AND fake_church !=1
;

Attached is the flow I have created within alteryx. It obviously isn't correct.

Any ideas on how to make this formula live within a select statement in sql?