topic Re: Date Comparisons in Alteryx Designer Desktop Discussions

Date Comparisons

Brian32 — Thu, 24 May 2018 02:38:52 GMT

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.

Re: Date Comparisons

Joe_Mako — Tue, 06 Dec 2016 18:58:10 GMT

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!

Re: Date Comparisons

Brian32 — Tue, 06 Dec 2016 19:37:43 GMT

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.

Re: Date Comparisons

Brian32 — Tue, 06 Dec 2016 19:39:08 GMT

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!

Re: Date Comparisons

Joe_Mako — Tue, 06 Dec 2016 20:26:18 GMT

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!

Re: Date Comparisons

Brian32 — Tue, 06 Dec 2016 20:38:31 GMT

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?

Re: Date Comparisons

Joe_Mako — Tue, 06 Dec 2016 21:03:33 GMT

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:

Re: Date Comparisons

Brian32 — Tue, 06 Dec 2016 21:58:11 GMT

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

Re: Date Comparisons

Joe_Mako — Tue, 06 Dec 2016 22:01:05 GMT

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?

Re: Date Comparisons

Brian32 — Tue, 06 Dec 2016 22:07:09 GMT

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?

Re: Date Comparisons

Joe_Mako — Tue, 06 Dec 2016 22:12:11 GMT

No worries, data types can be tricky, and sometimes it is difficult to keep track of what field is what.

After your above formula expression that compares the Date data type values, you can change them to strings with the DateTimeFormat function to get he display that you want.

Re: Date Comparisons

Brian32 — Tue, 06 Dec 2016 22:18:26 GMT

I think I am missing something here too then cause this nulls me out again.

Re: Date Comparisons

JessicaS — Wed, 07 Dec 2016 20:21:41 GMT

Hi @Brian32,

It sounds like you may need further support on your questions. If this is the case please email into support@alteryx.com and we will be happy to assist you.

Thanks,