I am stuck on one piece of a workflow and I'm hoping someone in the community can help me. In my data set, there are letter codes (example: AB, CD, EF, GH...) with date/time stamps that are related to a Ref# (example: 12345678). Also in the data set are "key" codes indicated with "XX". For each letter code (AB, CD, EF, GH...) I need to determine the first date/time stamp of the XX code that occurred after the letter code.
In other words, the letter codes (example: AB, CD, EF, GH...) signify events. The XX code indicates that the event was acknowledged. I need to determine the first acknowledgement date/time that occurred after the event. If not acknowledged yet, I need to indicate null or "NO XX".
Difficult to explain so I have prepared some examples:
Scenario 1
Ref# | Date | Time | Code | 1ST XX DATE | 1ST XX TIME |
12345677 | 7/10/2017 | 18:40 | AB | NO XX | NO XX |
(No XX found for this Ref# in the data. "No XX" returned in last two columns)
Scenario 2
Ref# | Date | Time | Code | 1ST XX DATE | 1ST XX TIME |
12345678 | 7/11/2017 | 18:40 | AB | 7/11/2017 | 18:41 |
12345678 | 7/11/2017 | 18:41 | XX |
(Most common scenario. Event AB occurred at 18:40 and was acknowledged by XX event at 18:41. The XX date/time is returned in last two columns.)
Scenario 3
Ref# | Date | Time | Code | 1ST XX DATE | 1ST XX TIME |
12345679 | 7/12/2017 | 19:40 | CD | 7/12/2017 | 19:41 |
12345679 | 7/12/2017 | 19:41 | XX | ||
12345679 | 7/12/2017 | 19:42 | XX | ||
12345679 | 7/12/2017 | 19:43 | XX |
(Event CD occurred and was acknowledged 3 times. Only the first XX date/time is returned. The others are irrelevant.)
Scenario 4
Ref# | Date | Time | Code | 1ST XX DATE | 1ST XX TIME |
12345683 | 7/13/2017 | 7:08 | EF | 7/13/2017 | 7:09 |
12345683 | 7/13/2017 | 7:09 | XX | ||
12345683 | 7/13/2017 | 8:10 | EF | 7/13/2017 | 8:11 |
12345683 | 7/13/2017 | 8:11 | XX |
|
(Two events occurred on this Ref#. The second EF is considered a new event, therefore, the first XX date/time after the 2nd event is returned. This may happen many times with an event followed by an acknowledgement or multiple acknowledgements. The letter codes may change each time and are not always the same. Example: each event could be any combination of letter codes except XX.)
Scenario 5
Ref# | Date | Time | Code | 1ST XX DATE | 1ST XX TIME |
12345684 | 7/13/2017 | 2:42 | AB | 7/13/2017 | 2:43 |
12345684 | 7/13/2017 | 2:43 | XX | ||
12345684 | 7/13/2017 | 3:22 | CD | 7/13/2017 | 3:23 |
12345684 | 7/13/2017 | 3:23 | XX | ||
12345684 | 7/13/2017 | 3:24 | XX | ||
12345684 | 7/13/2017 | 4:55 | CD | NO XX | NO XX |
(This combines scenario 1 and scenario 5. The last event CD at 4:55 has no XX event that occurred after the 2nd CD was entered and therefore "NO XX" is returned.)
Scenario 6
Ref# | Date | Time | Code | 1ST XX DATE | 1ST XX TIME |
12345685 | 7/16/2017 | 8:12 | AB | 7/16/2017 | 8:13 |
12345685 | 7/16/2017 | 8:13 | XX | ||
12345685 | 7/16/2017 | 3:22 | AB | 7/16/2017 | 3:23 |
12345685 | 7/16/2017 | 3:23 | XX | ||
12345685 | 7/16/2017 | 3:24 | XX | ||
12345685 | 7/17/2017 | 4:55 | CD | 7/17/2017 | 4:57 |
12345685 | 7/17/2017 | 4:56 | EF | 7/17/2017 | 4:57 |
12345685 | 7/17/2017 | 4:57 |
(Two additional events occurred on 7/17/17. The XX at 4:57 is considered acknowledgement for both events CD and EF. XX at 4:57 is still the first XX that occurred after both the CD and EF events.)
Attachment:
Scenarios: Lists the scenarios posted above
Data In: Sample of data input based on scenarios provided
Data Out: Sample of desired output based on scenarios provided
More Sample Data: Additional sample data (400 records)
My coworker suggested the Tile tool. I would be very interested to see if that tool could be applied to these scenarios.
Solved! Go to Solution.
How about something like the attached?
- Record ID to keep track of the original sort order, and create a unique identifier
- Sort on Ref# Ascending, and then Date and Time Descending
- Multi-Row Formula tool to calculate "1st XX Record ID" with the logic:
IF [Code]="XX" THEN Null() ELSEIF [Row-1:Code]="XX" THEN [Row-1:RecordID] ELSE [Row-1:1st XX Record ID] ENDIF
- Join Multiple to bring in the 1st XX Date and Time
- Filter to remove the the unmatched records
- Sort to return to original record sequence
BRILLIANT! Problem Solved. Thank you very much!