topic Re: IF AND in Alteryx Designer Desktop Discussions

IF AND

HM — Thu, 24 May 2018 01:28:11 GMT

Hi,

I'm trying to insert a formula which can lookup if it is null in 2 fields for the same row/record and if it is then output "No" else output "Yes" --> IIF(IsNull([Field 1]) AND (IsNull([Field 2]),"No","Yes")

The error I keep receive is "Malformed If statement".

Any help is appreciated - thanks.

Re: IF AND

MarqueeCrew — Tue, 27 Oct 2015 01:48:22 GMT

Looks reasonable, but try

IIF(isnull([field1]) && isnull([field 2]),"No","Yes")

Re: IF AND

HM — Tue, 27 Oct 2015 02:12:58 GMT

It worked perfectly! Thank you for that

Re: IF AND

MarqueeCrew — Tue, 27 Oct 2015 02:30:15 GMT

Great. If you wouldn't mind, could you mark that as solved?

thanks,

mark

Re: IF AND

brij — Tue, 06 Mar 2018 10:02:03 GMT

HI,

I'm getting following error.

IIF(isnull("Part No"),"Include","Exclude")

Error: Formula In-DB (32): Error SQLPrepare: [EXASOL][EXASolution driver]function or script IIF not found [line 6, column 20] (Session: 1594182034651602363)

Re: IF AND

LordNeilLord — Tue, 06 Mar 2018 10:07:31 GMT

@brij

It doesn't look like Exasol supports IIF.

You probably need to use a standard SQL IF

IF "Part No" is Null Then 'Include' Else 'Excude' End

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Re: IF AND

brij — Tue, 06 Mar 2018 10:17:53 GMT

ok thanks so could be Exasol doesnt support but I'm working on Alteryx WF it should have created the column in WF and posted the new column in Exasol.

However here is what I'm trying and still getting the error.

if "Billings$"<"BillingsCost$"
then "Billings$"-"BillingsCost$"
else 0
endif

error Im getting-

Error: Formula In-DB (32): Error SQLPrepare: [EXASOL][EXASolution driver]syntax error, unexpected IF_ [line 6, column 20] (Session: 1594182456386870715)

Re: IF AND

LordNeilLord — Tue, 06 Mar 2018 10:28:07 GMT

@brij

You're using the In-DB tools which need to be coded in the native language of the database, which in this case is Standard SQL

More info is here: https://help.alteryx.com/9.5/LockInFormula.htm

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Re: IF AND

brij — Tue, 06 Mar 2018 10:30:11 GMT

ahh great thanks this makes sense to me now.

ANy thoughts on error message m getting on if formula?

Brij

Re: IF AND

LordNeilLord — Tue, 06 Mar 2018 10:37:09 GMT

The error is caused by the format if your IF statement....the way it is written is for Alteryx..which is not the same as SQL.

It will need something like:

if "Billings$"<"BillingsCost$"
then "Billings$"-"BillingsCost$"
else 0
end

I'm not a SQL coder so I'm not 100% sure of the correct syntax

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Re: IF AND

brij — Tue, 06 Mar 2018 10:41:56 GMT

okay just tried case and that works

case
when "Billings$"<"BillingsCost$" then
"Billings$"-"BillingsCost$"
else 0
end

However IF has some bug in alteryx maybe .

Thanks for all the support so far.

Brij

Re: IF AND

papalow — Wed, 17 Oct 2018 22:43:53 GMT

Thanks for posting this, can you use && in a regular IF THEN ELSE ?

Re: IF AND

Matt_Sinker — Mon, 24 Dec 2018 09:43:02 GMT

Hi There,

Taking the same logic, how would I go about writing a formula with 3 conditions.

I am trying to replicate an excel formula which would read:

=IF(AND([X]="Y",[X]="N",[X]="N"),"Y","N")

Tried using the IF ELSEIF function but this isn't right, any thoughts?

Re: IF AND

Matt_Sinker — Mon, 24 Dec 2018 09:45:52 GMT

=IF(AND([X]="Y",[X1]="N",[X2]="N"),"Y","N") - would make more sense, apologies.

Re: IF AND

Matt_Sinker — Mon, 24 Dec 2018 10:24:00 GMT

Solved guys.

I didn't realise you could simply use AND in an IF THEN ELSE formula in Alteryx.

IF ([X]="Y" AND [X1]="N" AND [X2]="N") THEN "Y" ELSE "N" ENDIF - Worked.