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!
