Creating subgroups within the data fields such as Transaction id and timestamp

[kimwells]

I am trying to automate the financial audit analysis.
I need to mark the transactions for non compliance for internal rules. The last data field is Dollars. (Cannot enter).

This is a daily file and we only look at the daily compliance.

The audit criteria is followed by few rules. There is no identify of an individual person for these data but we can suspect that a paticular person may conduct multiple transactions based on the sequence of Trans ID and/or Time Stamp and/or POS Trans.

1. I need to make groups of transactions within a store, based on

    1.1 It will be by Store

    1.2 same Trans ID - This could have multiple dollars as a sub transaction

    1.3 We look at time stamp and comparing T2 and if the T2 time gaps are less than 5 or 10 minutes, we want to group them

    1.4 We look at Trans ID and if the gap between the Trans ID < 5 or 10, we want to group them

    1.5 I converted Trand ID to number, and Time to the time format and calculated time gap for comparison to the previous record and the next record.

    1.6 A group of transactions must be under $3,000 and my ultimate goal is to filter out the list of record groups, which might be the violation of the audit and send them back to validation.

I thought multi row tool and fussymatch or group tool may do it but cannot find a good solution. Hope someone will help me.

What I am struggling is to somehow create a field to segregate the records group under the each store, so I can do various calculations and comparisons.

I am attaching my WIP file as a attachment for you. 

Trans ID	Date	Time	Store	Register	POS Trans	cashierunmasked
-672223844	8/23/2023	817	1674	3	3640309	1798673	$500.00
-672223843	8/23/2023	819	1674	3	3640310	1798673	$500.00
-672223842	8/23/2023	820	1674	3	3640311	1798673	$500.00
-672223841	8/23/2023	821	1674	3	3640312	1798673	$500.00
-672223837	8/23/2023	909	1674	3	3640322	1798673	$500.00
-672223836	8/23/2023	911	1674	3	3640323	1798673	$500.00
-672223816	8/23/2023	1053	1674	3	3640345	1772151	$500.00
-672223815	8/23/2023	1054	1674	3	3640346	1772151	$500.00
-672223746	8/23/2023	1913	1674	3	3640424	1790882	$500.00
-672223745	8/23/2023	1914	1674	3	3640425	1790882	$500.00
-672223744	8/23/2023	1915	1674	3	3640426	1790882	$500.00
-672223743	8/23/2023	1916	1674	3	3640427	1790882	$500.00
-672219768	8/23/2023	1158	1736	2	2426877	849536	$350.00
-672219742	8/23/2023	1351	1736	2	2426906	849536	$500.00
-672219741	8/23/2023	1352	1736	2	2426907	849536	$500.00
-672219740	8/23/2023	1353	1736	2	2426908	849536	$500.00
-672219739	8/23/2023	1355	1736	2	2426909	849536	$500.00
-672219738	8/23/2023	1358	1736	2	2426910	849536	$500.00
-672219737	8/23/2023	1359	1736	2	2426911	849536	$198.60
-672219697	8/23/2023	1539	1736	2	2426957	1812922	$132.00