Replacing Non Null Values

[irajesh2017]

Hello Everyone,

I am a very very new user. Just started working with Alteryx 2 days before.

I need to create a calculated column like this.

If

column A is not Null

or

Column b is not null

then 1 else 0

Need help from expert.

Thanks!

regards

Rajesh

[MarqueeCrew]

Please try

IIF(IsNull([A]) or IsNull([B]),1,0)

Cheers,

Mark

[LordNeilLord · Accepted solution]

Hey @irajesh2017

Welcome to Alteryx :)

Try 

IF !IsNull(A) || !IsNull(B) then 1 Else 0 Endif

[LordNeilLord]

Or what @MarqueeCrew said

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[LordNeilLord]

Any performance differences between IIF and IF endIF @MarqueeCrew?

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[irajesh2017]

IIF(IsNull([A]) or IsNull([B]),1,0)

Does it not mean that it will replace Null with 1.

I want if any of value is NOT NULL then replace with 1 else 0. Basically I want to sum of all values which are not NULL.

Therefore replacing NULL with 0 to have no effect in SUM.

Thanks for your prompt reply.

[LordNeilLord · Accepted solution]

@irajesh2017

To make it NOT NULL add an exclamation mark...!IsNull()

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[MarqueeCrew]

In this case no.  

For simple IF THEN ELSE statements, I prefer the use of the IIF statement.  For performance, the IF condition order can be important.  When asking if and elseif about the data, you want to get to the top condition(s) first.  That way you avoid computations.

Cheers,

Mark

[irajesh2017]

Hi LordNeilLord,

Thanks for your prompt response.

Code is working. Just need one clarification.

Do '2 Pipes' in between means 'OR' in this logic.

If I have many fields with or and AND logic, then how to use AND.

There are too many calculated fields which I need to do it. I will be using something like this. Basically there are combinations of AND or OR with IN function.

case

when (e.dedup_flag = 'Deduped' or complete1 = 1) and ever_approve1 = 1 and final_approve1 = 0 and accepted1 = 0 and CREDIT_STATUS not in ('Credit Expired','Credit Denied','Hold Credit Request','Borrower Requires CoApp') and LOAN_CODE in ('PCL996','PCL993') and PNOTE_STATUS in ('Awaiting Prom Note','Expecting Prom Note','Go To Print','Received Prom Note - Complete') and appworks_status not in ('Awaiting Document/Review') then 1 else 0 end as AwaitingSigAndTerms

Hoping to get some help.

Thanks!

[LordNeilLord · Accepted solution]

Hey @irajesh2017

You are correct || is the same as OR and && is the same as AND (both work in Alteryx)..

Your SQL statement as it is should be a fairly straight forward transfer over:

IF (e.dedup_flag = 'Deduped' or complete1 = 1) and ever_approve1 = 1 and final_approve1 = 0 and accepted1 = 0 and CREDIT_STATUS not in ('Credit Expired','Credit Denied','Hold Credit Request','Borrower Requires CoApp') and LOAN_CODE in ('PCL996','PCL993') and PNOTE_STATUS in ('Awaiting Prom Note','Expecting Prom Note','Go To Print','Received Prom Note - Complete') and appworks_status not in ('Awaiting Document/Review') then 1 else 0 endif

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome