Alteryx can't seem to find a date between a range

I need to create an alteryx workflow that can identify dates that lay within a date range i give it.

Input 1 will be all of the dates I have and the corresponding account numbers that go with them. There can be multiple dates on a single account number.

Input 2 will be a static date and corresponding account number, there will be no duplicates here.

I need to find dates from Input 1 that lay between -7 and 120 days from Input 2

When I do this, I do a formula tool

DateTimeDiff([Input 1], [Input 2], "days")

and then a filter

[DateDifference] >= -7 AND [DateDifference] <= 120

Alteryx never returns the right dates. It gives me ones that lay outside of the specified range.

Datasets

Accepted answers

apathetichell

Dates coming in from raw data are formatted as date, --- is wrong.

learn about your raw data has dates in two formats --- standard (yyyy-mm-dd) and some kind fo time stamp with (mm/dd/yyyy) ---> in the same column. It comes in as vstring - not as date. If this is rouced from excel - check the type in excel. My hunch is someone combined two data sources. one is date -> one is not.

short is -> drop a multi-field formula tool -> select your medicare ra date column -> change it from vstring to date. unselect create new column.

put this in the formula:

if regex_match([_CurrentField_],"\d{4}-\d{2}-\d{2}") then todate([_CurrentField_]) else datetimeparse([_CurrentField_],"%m/%d/%Y") endif

re run. if you are not seeing what you expect - you have invereted your dates in your date time difference. The above formula is the correct formula to parse either yyyy-mm-dd or mm/dd/yyyy to date -> in the same column. It will not parse dd/mm/yyyy. Here are the 23 records breaking your process:

18159 08/30/18 2818
32760 09/25/18 2818
52583 11/12/18 2818
34921 12/13/18 2818
61292 01/04/19 2818
51998 01/24/19 2818
49336 02/25/19 2818
84112 11/23/22 573
85820 04/27/23 573
53148 04/27/23 573
40849 09/29/23 8297
39594 09/25/23 3864
30485 09/26/23 1235
96451 09/14/23 31066
26147 09/27/23 851
65120 09/25/23 26698
3501 09/26/23 48143
40991 09/26/23 13960
87068 09/25/23 2352
4219 09/19/23 44994
79837 09/26/23 44994
1775 09/28/23 282
72045 09/21/23 318

All comments

KGT

I'm not sure without seeing an example as to why yours is not working. I assume it's something small, but just can't see it. Take a look at the diffs it returns and try figure out how that is being calculated.

If Input 2 is unique on account number, then I would create 2 extra fields on that for the start and end, then join that to Input 1 and apply a filter for >Start and <End.

You could also join Input 2 on Account number, then use a filter with the following:

[Input2Date] >= DateTimeDiff([Input1Date],-7,'day') AND [Input2Date] <= DateTimeDiff([Input1Date],120,'day')

apathetichell

Can you provide some examples? This sounds like you are doing something wrong which can be fixed pretty easily - but it's hard without data/examples.

WishIKnewHowToCode

Here's the problem, this is technically patient data. So, I have to create dummy values. I dummied one input and copied the numbers many times over on the other. Hopefully, it can still "solve" with the dummy data. One very annoying aspect of the type of work I do.

Copy of Traditional Remit Solver.yxzp

WishIKnewHowToCode

I posted a workflow and some context in response to the other person.

Qiu

@WishIKnewHowToCode
You can not really blame Alteryx, since your "medicare RA Date" is not in Date format.

Copy of Traditional Remit Solver-r1.yxzp

apathetichell

My hunch is that you are converting your dates incorrectly somewhere -> @Qiu or @KGT will point you in the right direction but the first rule of using datetime is ---- make sure you are actually comparing dates. In your case I think something converted wrong.

WishIKnewHowToCode

Sorry, passed out last night.

Your workflow still produces a result in error. I used the real data and got a result that is -50 days away from the DOFB input. I remember running into this issue last time I did this months ago (ended up giving up back then). I remember trying to tell alteryx that it needed to be 7 and -120 instead of -7 and 120, but the results were still wrong.

apathetichell

@WishIKnewHowToCode -> my hunch -> your dates don't all fit the same schema. They are sourced from different upstream sources and some show up as dd/mm/yyyy some might be mm/dd/yyyyy - and some might be yyyy-mm-dd. you have a data problem - not an Alteryx problem. Identify and fix and your workflow will be fine.

EKasminsky

A good way to check if your dates are not formatted right is put a select tool next to them, change the data type to "Date" and then see if there are errors. You could filter dates with "/" into one group, convert them using a formula tool, then union the data with any dates that are in the format Alteryx likes the most "yyyy-mm-dd"

WishIKnewHowToCode

Dates coming in from raw data are formatted as date, but my formula tool changes it to double because it won't work otherwise. Can what formula could I use to keep them as dates?

apathetichell

I'd have to see the raw data. but there is never. never. never. a reason to change them to a double. I actually try to use double as little as possible.

WishIKnewHowToCode

Can't you just see the example I gave? How can I fix my formula to not use Double?

apathetichell

Dates coming in from raw data are formatted as date, --- is wrong.

short is -> drop a multi-field formula tool -> select your medicare ra date column -> change it from vstring to date. unselect create new column.

put this in the formula:

if regex_match([_CurrentField_],"\d{4}-\d{2}-\d{2}") then todate([_CurrentField_]) else datetimeparse([_CurrentField_],"%m/%d/%Y") endif

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703