Create Filter Based on Specific Value and Date Range

[atroupebaker]

Hello there! I hope I am not posting something that has already been asked, but I have been searching for days for a solution with no luck. I will do my best to describe what I am looking for and appreciate any help in advance. I am fairly new to Alteryx and this is my first post so please bear with me!

I have a specific field that is populated with values identifying how members come to my credit union. I want to exclude new members using a date field based on this text field so they are not included in my final output file. Here is what I am trying to use as my formula to create a flag to filter on, but I am receiving a malformed statement error on the ENDIF portion. I am sure I am doing something silly, but I have been staring at this filter for so long and have tried so many variations that I don't know what else to try at this point.

IF [Member SEG] ="INDMORT" OR [Member SEG] = "INDIRECT MORTGAGE" AND [Share Open Date]>=DateTimeAdd(DateTimeToday(),-14,'days') THEN "Disqualified" ELSEIF [Member SEG] = "%" THEN "KEEP" ENDIF

Thanks again!

[BrandonB]

You need to have an ELSE statement in your formula in order to do something with records that don't meet any of your other criteria. After you include an ELSE with this evaluation you won't have an error. 

[DavidP · Accepted solution]

Welcome to the Community!

Two things:

1. Every if statement must have an ELSE before the ENDIF

2. I would put brackets around the 1st 2 conditions - it's like combining +'s and *'s in a formula, 1+2*3 is different to (1+2)*3 and it's the same with OR and AND

So the statement becomes

IF ([Member SEG] ="INDMORT" OR [Member SEG] = "INDIRECT MORTGAGE") AND [Share Open Date]>=DateTimeAdd(DateTimeToday(),-14,'days') THEN "Disqualified"
ELSEIF [Member SEG] = "%" THEN "KEEP"
ELSE "" ENDIF

[rupali_bhise]

Hey @atroupebaker ,

You forgot to add "else" condition at the end. 

Basic IF ELSE IF formula is "IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF"

Updated formula:

IF [Member SEG] ="INDMORT" OR [Member SEG] = "INDIRECT MORTGAGE" AND [Share Open Date]>=DateTimeAdd(DateTimeToday(),-14,'days') THEN "Disqualified" ELSEIF [Member SEG] = "%" THEN "KEEP" ELSE null() ENDIF

You can replace/add null() with anything that suits your condition.

Good luck.

[atroupebaker]

Thank you for the response Brandon!

[atroupebaker]

David - I appreciate the correction to my statement. I used your update because it most closely matches how I write my other statements and my error is gone, thanks! 

[atroupebaker]

Thanks so much for listing the IF statement for me rupali and for the assistance! Very helpful.