Summarizing Multiple Timestamps

Question

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 IDAuditMessageTimestampABCOnHold2/8/2018 4:40ABCRevertedOnHold3/6/2018 14:22ABCOnHold6/8/2018 4:40ABCRevertedOnHold7/6/2018 14:22DEFOnHold6/25/2018 7:17DEFRevertedOnHold6/29/2018 7:08GHIOnHold5/26/2018 18:33GHIOnHold5/26/2018 18:41GHIOnHold5/26/2018 18:45GHIOnHold5/26/2018 18:51GHIOnHold5/26/2018 18:53GHIOnHold5/26/2018 18:59GHIRevertedOnHold5/26/2018 18:35GHIRevertedOnHold5/26/2018 18:42GHIRevertedOnHold5/26/2018 18:49GHIRevertedOnHold5/26/2018 18:52GHIRevertedOnHold5/26/2018 18:55GHIRevertedOnHold5/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 IDOn Hold 1On Hold 2On Hold 3On Hold 4On Hold 5On Hold 6Revert On Hold 1Revert On Hold 2Revert On Hold 3Revert On Hold 4Revert On Hold 5Revert On Hold 6ABC2/8/2018 4:406/8/2018 4:40    3/6/2018 14:227/6/2018 14:22    DEF6/25/2018 7:17     6/29/2018 7:08     GHI5/26/2018 18:335/26/2018 18:415/26/2018 18:455/26/2018 18:515/26/2018 18:535/26/2018 18:595/26/2018 18:355/26/2018 18:425/26/2018 18:495/26/2018 18:525/26/2018 18:555/26/2018 19:00

Any help is much appreciated!

NickJ · Accepted Answer

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