This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!
Hello,
I have not been able to find the solution to this in the history, and I hope that someone can help. I need to summarize multiple timestamps to determine how much time a single order was put "on hold".
This is the source data:
Order ID | AuditMessage | Timestamp |
ABC | OnHold | 2/8/2018 4:40 |
ABC | RevertedOnHold | 3/6/2018 14:22 |
ABC | OnHold | 6/8/2018 4:40 |
ABC | RevertedOnHold | 7/6/2018 14:22 |
DEF | OnHold | 6/25/2018 7:17 |
DEF | RevertedOnHold | 6/29/2018 7:08 |
GHI | OnHold | 5/26/2018 18:33 |
GHI | OnHold | 5/26/2018 18:41 |
GHI | OnHold | 5/26/2018 18:45 |
GHI | OnHold | 5/26/2018 18:51 |
GHI | OnHold | 5/26/2018 18:53 |
GHI | OnHold | 5/26/2018 18:59 |
GHI | RevertedOnHold | 5/26/2018 18:35 |
GHI | RevertedOnHold | 5/26/2018 18:42 |
GHI | RevertedOnHold | 5/26/2018 18:49 |
GHI | RevertedOnHold | 5/26/2018 18:52 |
GHI | RevertedOnHold | 5/26/2018 18:55 |
GHI | RevertedOnHold | 5/26/2018 19:00 |
I need to transform it to look like the below. I would like to dynamically add a column each time a single order goes beyond the current limit if possible, but that is not necessary.
Order ID | On Hold 1 | On Hold 2 | On Hold 3 | On Hold 4 | On Hold 5 | On Hold 6 | Revert On Hold 1 | Revert On Hold 2 | Revert On Hold 3 | Revert On Hold 4 | Revert On Hold 5 | Revert On Hold 6 |
ABC | 2/8/2018 4:40 | 6/8/2018 4:40 | 3/6/2018 14:22 | 7/6/2018 14:22 | ||||||||
DEF | 6/25/2018 7:17 | 6/29/2018 7:08 | ||||||||||
GHI | 5/26/2018 18:33 | 5/26/2018 18:41 | 5/26/2018 18:45 | 5/26/2018 18:51 | 5/26/2018 18:53 | 5/26/2018 18:59 | 5/26/2018 18:35 | 5/26/2018 18:42 | 5/26/2018 18:49 | 5/26/2018 18:52 | 5/26/2018 18:55 | 5/26/2018 19:00 |
Any help is much appreciated!
Solved! Go to Solution.
Hi!
If I've understood the requirement here, you need a couple of data preparation tools to frame the data correctly before you drop in a 'Crosstab' tool to pivot the table into the format you have below.
I've used a Multi-Row formula tool to work out the order of occurrence of each status before the Cross-Tab tool. I then used a regular Formula tool to get the formatting of the columns correct.
Hope this helps!
Nick
works perfectly! Thank you!