Summarizing Multiple Timestamps

[mikeyd1276]

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!

[NickJ · Accepted 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

[mikeyd1276]

works perfectly!  Thank you!