Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Create Filter Based on Specific Value and Date Range

atroupebaker
7 - Meteor

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!

6 REPLIES 6
BrandonB
Alteryx
Alteryx

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
17 - Castor
17 - Castor

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
8 - Asteroid

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
7 - Meteor

Thank you for the response Brandon!

atroupebaker
7 - Meteor

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
7 - Meteor

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

Labels