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

Difference between timestamps

MCDR929
8 - Asteroid

Hello!

I have a workflow that generates an email with timestamps by warehouse and ship method (example below).

What I need is a way to generate a time difference, so, for example, the difference between the timestamp between two stamps in the DC1 column, two in DC2, etc.  

My end goal is to light the column up in red if the time stamps are too close together, say, within 4 minutes of each other.

I don't know where to begin with this. I'm good on creating colors for a column based on a value, but I'm struggling with how to get the time differences for each column's two chosen timestamps.

Thanks in advance!

 

CarrierDC1DC2DC3
FEDEX (EXPEDITED)   
FEDEX (EXPEDITED)8/3/2018 18:01  
FEDEX (EXPEDITED)   
FEDEX (EXPEDITED)8/3/2018 18:56 8/3/2018 18:32
FEDEX (EXPEDITED)   
FEDEX (EXPEDITED) 8/3/2018 21:33 
FEDEX (EXPEDITED)   
FEDEX GROUND  8/3/2018 20:19
FEDEX GROUND 8/3/2018 21:34 
FEDEX GROUND   
FEDEX GROUND8/3/2018 22:40  
FEDEX GROUND   
7 REPLIES 7
ivoller
12 - Quasar

Hi @MCDR929,

 

I'm not entirely sure I fully understand the issue. However, see attached for my take assuming you want to get the differences between a timestamp in a column with the preceding value.

 

2018-08-06_15-19-49.png

CharlieS
17 - Castor
17 - Castor

In the attached solution, I create a flag for each column using a Multi-Row Formula tool at the core of that process. After the flag columns are joined back to the original data, a Table tool is used to make formatting changes, such as filling the background color of the data values.

 

 

MCDR929
8 - Asteroid

Thank you!  I wouldn't have muddled my way through this in a decade, but this will be really useful in my learning process.  Thanks so much for the assistance!

MCDR929
8 - Asteroid

Thank you!  I believe your solution is very similar to the one that followed it.  I sincerely appreciate the help.

MCDR929
8 - Asteroid

@CharlieS @ivoller - followup question:

 

I'm working my way through both of these, but now I have to look at it slightly differently.  I now have a column (we'll say column 2) that has a time on every line.  For each DC, there may or may not be a timestamp in their respective columns.  For the ones that DO have a time stamp, I need to compare that stamp to the time in column 2.  Where times are different by more than 29 minutes, they need to be flagged red.

 

I've been trying to adapt the provided workflows to achieve this, but I'm stumbling on the multi-row formula. I don't see a multi-column formula tool, but I see a multi-field formula. Can either of you just steer me in the right direction for comparing times from rows to their corresponding time in column 2?

ivoller
12 - Quasar

I think I may be missing something but can't you just use a regular formula for this? I.e. after converting everything to the appropriate date formats etc.for each DC have a column DCx Diff creatd from a formula like

 

Iif(!isnull([DCx]),datetimediff([DCx],[Column 2],"minutes"),null())

MCDR929
8 - Asteroid

@ivoller - I was definitely over-thinking that.  Thank you!

Labels