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

Compare dates from different rows and return Date difference in a new column

GusIbar
6 - Meteoroid

Hi!

 

I've searched for this issue but haven't found a response I can implement (mostly because I am new to Alteryx) so I am seeking your advice.

 

I have to find out what is the time difference in days between two dates. My complication is that these dates are in two different rows and the comparison needs to happen only if the value on a third column is duplicated. I attempted to use Multi-Row Formula, Cross Tab, and other stuff but can't manage to make it work. The outcome of all this has to be that if the date difference is over 90 days both records are valid, if under 90 days only the older record is valid.

 

Here is an example of my data set:

Record IDDate (DD/MM/YYYY)Date DiffDesired outcome

1

01/01/2019 Valid
201/01/2019 Valid
301/01/2019 Valid
301/05/2019120 daysValid
401/01/2019 Valid
501/01/2019 Valid
501/02/201931 daysNot valid
601/02/2019 Valid

 

The column Date Diff is optional, I only added it to exemplify the logic I am trying to build.

 

I have no idea how to build it in Alteryx or what tool should i use any help is appreciated, please be as through as possible with your explanation, I've only spent a few hours with the tool.

 

Thanks in advance!

5 REPLIES 5
Claje
14 - Magnetar

Hi,

I Could use a little more clarity in your example - do these dates properly tie to your desired outcomes?

Also, do you only want it to compare within the same record ID (so all records with an ID of 3 get compared together), or all records to be compared regardless of ID?

GusIbar
6 - Meteoroid

Hi Claje, thanks for the quick reply.

 

The dates will vary and can be any date, I only used those as an example. But they could be 02/27/2018 or 03/04/2019 or anything other date. They aren't fixed, these are time stamps on a series of events the report I am using as the date source captures.

 

Yes, It should only compare dates if the ID is identical. It is very unlikely that there will be 3 identical record ID, more often it will only be two records sharing the same ID.

 

Hope this clarifies things a bit.

Claje
14 - Magnetar

My own confusion! I'm used to MM/DD/YYYY date formats and so I was reading your dates as being 1-5 days apart, not 1-5 months!

 

I've attached an example (with slightly different field names) here that should work.  The key pieces were converting your Date field into an Alteryx date type using DATETIMEPARSE, and using the GROUP BY functionality in the Multi-Row Formula tool.


Take a look at the attached and let me know if you have any questions!

Thableaus
17 - Castor
17 - Castor

Hi @GusIbar 

 

Would this work?

 

Work1.PNG

 

The logic works this way - it's seeing if there are duplicates before the actual row. So, it's actually making comparisons with the row before.

 

See workflow appended and let me know if there any doubts.

 

Cheers,

GusIbar
6 - Meteoroid

Thank you both!

 

The answers are very similar and this not only help me solbve this complication but also I learned how to use the Multi Row Formula tool!

 

Thanks again!

Labels