We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Nested If Statement

Alefiyah
5 - Atom

Hello, 

 

I'm trying to write a nested IF statement, in which the datediff is either calculated between a creation date and the completion date, or in the event that the completion date does not exist, then the creation date and the current date. 

 

My code is as follows (however I am receiving a 'malformed if statement' error using it): 

IF ISNULL([Completion by Date]) THEN
IF DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff(DateTimeToday(), [Created on],'days') > 45
THEN 'Near Aging'
ELSE Null()
ENDIF
ELSEIF !ISNULL([Completion by Date]) THEN
IF DateTimeDiff([Completion by Date],[Created on],'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff([Completion by Date], [Created on],'days') > 45
THEN 'Near Aging'
ELSE Null()
ENDIF

 

Any help is greatly appreciated. 

 

Thanks, 

 

Alefiyah

4 REPLIES 4
IraWatt
17 - Castor

Hey @Alefiyah,

There is a few things going on here. First statments like

IF ISNULL([Completion by Date]) THEN
IF DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'

Can be combined into (for simplification):

IF ISNULL([Completion by Date]) AND DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'

And second you have two IF statements separate from each over so eg. you ENDIF in the middle. I don't think Alteryx can interpret this as you are just filling One value.

 

Try this:

IF ISNULL([Completion by Date]) AND DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff(DateTimeToday(), [Created on],'days') > 45
THEN 'Near Aging'
ELSEIF !ISNULL([Completion by Date]) AND
DateTimeDiff([Completion by Date],[Created on],'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff([Completion by Date], [Created on],'days') > 45
THEN 'Near Aging'
ELSE Null()
ENDIF

IraWatt_0-1663005352592.png

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...

 

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

 

 

Qiu
19 - Altair
19 - Altair

@Alefiyah 
May I comment that for conditional statement, I believe the point is that it should alwasy cover all the conditions.
in you case, > 59 is condition1, then > 45 is condition2 so the correct way should be 

1. condition1: A >59

2. Condition 2: A<= 59 and A>45

3. Condition 3: else

otherwise, Say a number 60 will alwas be trapped by condition 2.

0913-Alefiyah.PNG

Pang_Hee_Choy
10 - Fireball

@Qiu 

if the expression sort nicely. it will not a problem. but it still good to have for safety purpose.

Pang_Hee_Choy_0-1663058458901.png

and we can shorten the formula further, since the condition and text is same.
and better to add condition for less the 45 and blanks.

Pang_Hee_Choy_2-1663059968584.png

 

 



 

Qiu
19 - Altair
19 - Altair

@Pang_Hee_Choy 
Nice one.
I think the Switch function is better fit for this one. Thanks! 😉

Labels