community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

If Statement

Meteoroid

I am getting a 'malformed if statement' error on the below sequence - can anyone assist?  I think its with my "then if" statement... not sure that that nesting is possible.

 

if [curstat]=(1 OR 2 OR 3 OR 4 OR 5 OR 6 OR 7) then ([curstat]-1) else if [curstat]=(8 OR "S" OR "B" OR "L" OR "W") then 7 else if [curstat]="N" then (if ([DAYS_DEL]>=90 AND [DAYS_DEL]< 119) then 4 else if ([DAYS_DEL]>=120 and [DAYS_DEL]<149) then 5 else 6 endif endif) else 0 endif endif

 

Thanks!

Alteryx Certified Partner
Alteryx Certified Partner

Hi @kierabohan!

 

First of all, you should use the "elseif" expression without a whitespace, in a single word.

 

Second, you're mixing types in your expression

The field [curstat] must be a numeric or a string field, it can't be both. You cannot use math operations like "[curstat]-1" if you have strings on it. So I recommend you filtering numbers, change field type to numeric, doing the math operation with your formula, and then union the records again as strings, to finally apply your full expression.

 

Your if statement had some extra "endifs" on it, so I also corrected that.

if [curstat]=(1 OR 2 OR 3 OR 4 OR 5 OR 6 OR 7) then ([curstat]-1) elseif [curstat]=(8 OR "S" OR "B" OR "L" OR "W") then 7 elseif [curstat]="N" then (if ([DAYS_DEL]>=90 AND [DAYS_DEL]< 119) then 4 elseif ([DAYS_DEL]>=120 and [DAYS_DEL]<149) then 5 else 6 endif) else 0 endif

 

If you post your full workflow or your sample data, I'd be glad to work on a solution for you.

 

Cheers,

 

Meteoroid

Thank you @Thableaus! Both fields (curstat & days_del) ) are number fields so they can perform that calculation, does that mean I can't use the letters in this formula? ([curstat]="S" or "B" or "W")

Alteryx Certified Partner
Alteryx Certified Partner

@kierabohan

 

If they are numeric fields (like Int16, Int32, Double), the letters will be shown up as Nulls, since they are not numbers.

Like I said, you have to treat things separately.

Define the field as a string, filter numbers, change to numeric, apply your math, change back to string and then use the Union Tool to join the records back together (numbers and string), to compare each other with your full formula.

Meteoroid

I don't think that is the issue, because when I pull those nested if statements out into a new line, they operate correctly.  Using your formula, I am now getting an "invalid type in operator --." error

Alteryx Certified Partner
Alteryx Certified Partner

@kierabohan

 

The Invalid Type Operator error is associated with what I said. You are mixing field types, and that's going to cause this error.

Your formula has to deal either with string type or numeric type. Not both.

If you want to use number as strings, you have to quote them. But remember you cannot perform math operations with string types.

Aurora

Hi @kierabohan

 

As usual, all of @Thableaus about mixing types are valid.  But, there's also a logic error in your criteria clauses

 

 

If [curstat]=(1 OR 2 OR 3 OR 4 OR 5 OR 6 OR 7) then

This statement evaluates to 

 

[curstat]  =    the logical result of (1 OR 2 OR 3 OR 4 OR 5 OR 6 OR 7).  

 

The part in brackets is evaluated first and then compared to [curstat] which gives 

 

[Curstat] = true or [curstat] = -1, depending on if [Curstat] is Boolean or integer.

 

What you actually want here and in the other criteria clauses is 

 

If [curstat] in (1, 2, 3, 4, 5, 6, 7) then

The IN operator checks if [curstat] is any of the values in the brackets

 

Of course this will only work once you get the variable type problems fixed in the 2nd if statement

 

Dan

Labels