Subtracting Date
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
ID | Argument Name | Argument Value |
1097239 | START | 1/1/2020 18:49 |
1097239 | n/a | n/a |
1097239 | n/a | n/a |
1097239 | n/a | n/a |
1097239 | n/a | n/a |
1097239 | n/a | n/a |
1097219 | END | 1/1/2020 18:57 |
- Labels:
- Best Practices
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
to add to @JBO make sure you group by ID in the mutirow tool
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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_ID | AOG_START | AOG_END |
809875 | 6/30/2017 18:20 | |
813099 | 7/6/2023 18:32 | |
1080335 | 6/5/2019 19:57 | 6/5/2019 23:16 |
1093914 | 12/14/2023 18:05 | |
1109078 | 7/3/2020 16:55 | 7/3/2020 21:17 |
1125210 | 12/18/2021 14:57 | |
1125301 | ||
1125539 | 1/4/2021 11:43 | |
1125665 | 1/2/2021 22:05 | |
1125807 | 1/21/2021 21:14 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Just replied to you at the bottom of the thread