Tile Tool? How can I return the first date/time stamp following key code records?

[ddiesel]

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.

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

[ddiesel]

BRILLIANT! Problem Solved. Thank you very much!