Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Subtracting Date

Mwalden11
5 - Atom

Hey! I have this example below of an ID that has multiple values but in the Argument Name column there is a "start" and "end" date that I want to subtract. Anyone have any advice on how to do this?

 

IDArgument NameArgument Value
1097239START1/1/2020 18:49
1097239n/an/a
1097239n/an/a
1097239n/an/a
1097239n/an/a
1097239n/an/a
1097219END1/1/2020 18:57
5 REPLIES 5
JBO
8 - Asteroid

Yes. I would do the following:

1) Use the Filter tool to isolate rows that only have "START" OR "END" in the Argument Name field.

2) Use the Sort tool to sort the data in the order you want them (by ID and then Argument Value, for example).

3) Use the Multi-Row Formula tool. 

 

I am making some assumptions on the conditions, but here is the formula you could use:

 

if [ROW-1: Argument Name] = "START"

and [Argument Name] = "END"

then DateTimeDiff([Argument Value], [ROW-1: Argument Value], "minutes")

else null()

endif

 

4) Use the Union tool to bring the filtered rows back together again. Then use Sort tool if you want them sorted a certain way.

 

Note that this approach is putting the difference between values in a new field that you will need to give a field name and type.

Hope this helps. Sorry I didn't build the flow and upload.

 

If the ID #s are important to the condition, then you need to incorporate that into the formula. For example, if the Start ID# must be different from the end ID#, as they are in your example.

JBO
8 - Asteroid

I found a few seconds to pull together a flow. See below. I realized that you couldn't subtract without formatting the date field. Hope this helps.

aatalai
14 - Magnetar

to add to @JBO make sure you group by ID in the mutirow tool

Mwalden11
5 - Atom

Edited:

All good - figured this out. Thanks all.

 

OK Changing it up a little - if it is like this:

Instead of a multi Row formula would you just use a formula for this? 

 

ISSUE_IDAOG_STARTAOG_END
8098756/30/2017 18:20 
813099 7/6/2023 18:32
10803356/5/2019 19:576/5/2019 23:16
109391412/14/2023 18:05 
11090787/3/2020 16:557/3/2020 21:17
112521012/18/2021 14:57 
1125301  
1125539 1/4/2021 11:43
1125665 1/2/2021 22:05
1125807 1/21/2021 21:14
Mwalden11
5 - Atom

Just replied to you at the bottom of the thread 

Labels
Top Solution Authors