Date Comparisons

[Brian32]

Good Afternoon -

Been stuck on this for a while now.  Any guidance would be greatly appreciated and may save me from going bald in the next 12 hours!

I created an IF Statement that looks at a Date Column compares it to DateTimeToday() and then results in a V-String, returning 1 of 3 different values.

          IF [ProjectStatus]="Re Hold" THEN "RE HOLD"
          ELSEIF [ProjectStatus]="Site/REC Approved" && [Construction_Start]<DateTimeToday() && [Store_Open]>DateTimeToday() THEN "NS"
          ELSEIF [ProjectStatus]="Site/REC Approved" && [Construction_Start]<DateTimeToday() && [Store_Open]>DateTimeToday() THEN "CIP"
          ELSEIF [ProjectStatus]="Site/REC Approved" && [Construction_Start]<DateTimeToday() && [Store_Open]<DateTimeToday() THEN "OPEN"
          ELSE "" ENDIF

Currently the Database brings the dates in with the same format as Alteryx (yyyy-mm-dd) as a V_W String.

When I run the script I do not get the results I would expect using the logic above.  So I have done a number of things to play around with it and received various results, none of which worked though.

          DateTimeFormat([dt],"%m-%d-%y) for both my dates

          Created a new column called Today.  Formated to match the formating directly above and replaced DateTimeToday() in my IF Statement with [Today]

          Changed my data type for all my dates to Date

          Then I have done various combinations of these

Thank you in advance for your time and help.

[Joe_Mako]

Looks like the conditional statements for "NS" and "CIP" are the same, with your current logical statement I would expect no records result in CIP.

In plain English what logic would you like? That would help craft a statement to match what you are looking for. Thank you!

[Brian32]

Hi Joe,

Sorry about that.  I made an error when typing it in.  Plus I think I had a lot of unnecessary logic in there. This is my formula simplified.

IF [ProjectStatus]="Re Hold" THEN "RE HOLD"
ELSEIF [Construction_Start]>DateTimeToday() THEN "NS"
ELSEIF [Construction_Start]<DateTimeToday() && [Store_Open]>DateTimeToday() THEN "CIP"
ELSEIF [Store_Open]<DateTimeToday() THEN "OPEN"
ELSE "" ENDIF

I work in the Real Estate department for a Retailer.  What I want to happen is, on the day I run the report, if a Construction Start Date is in the future and therefore hasn't happened yet, then I want it to return a status of "NS" (Not Started).  If the Construction Start Date is in the past, but the Store Open Date is in the future and yet to occur, then I want it to return a status of "CIP" (Construction in Progress).  Lastly If the Store Open Date is in the past then I want it to return a status of  "Open".  The first part, "RE HOLD" is just looking for if a project is currently on hold and therefore we are not concerned about where it is at in the construction process.

[Brian32]

Also, I meant to add that OPEN is my current result.  It is as if it is not treating the value as a date for comparison.  If I switch the < and > signs I get NS.  Thanks!

[Joe_Mako]

Everything seems good in your new expression, see attached for an example. What is the data type of your date fields, are they Date data type? You can check this with a Select tool.

If they are date data type, and it is still not giving you the result you are looking for, can you attach a workflow with sample data, along with what results are incorrect and what you expect them to be? Thank you!

[Brian32]

Hey Joe,

Thanks again for helping.

Dates are coming in from the Database as a V_W String.  I then do DateTimeFormat([Construction_Start],"%m-%d-%y") and the same for [Store Open].  My IF statement then comes in after that.

Does doing a DateFormat not turn it to a date?

[Joe_Mako · Accepted solution]

See https://help.alteryx.com/10.6/index.htm#Reference/DateTimeFunctions.htm and expand the "Input and Output" section.

DateTimeFormat outputs a String data type

DateTimeParse outputs a Date data type that Alteryx can use

You will want to use the DateTimeParse function and have it be a Date Data Type, for example:

If you do not want to create a seperate field, and change the data type, you can use the Multi-Field Formula tool, like:

[Brian32]

I went ahead and did the MultiField Formula and that caused all of my Dates to go [Null].

[Joe_Mako]

What are some example string values before this step, are they inded:

01-31-16

or some other format?

Are all the fields you checked in the select box at top dates?

[Brian32]

Oh ok, I misunderstood the parse tool.  So to parse you are stating the format the date is in now?  I changed that and that worked.  Now after my calculation am I then safe to change the format to %m-%d-%y using DateTimeFormat?