Grouping transactions based on difference in dates

Anonymous — Sat, 31 Mar 2018 09:33:52 GMT

Hello all,

i've this use case where i would like to group transactions per person into groups based on the difference in dates between transactions. See table below on example dataset:

Person ID	Transaction ID	Transaction_Item	Transacted Date	Grouping
A	T_1	1	01/01/2018	1
A	T_1	2	01/01/2018	1
A	T_2	1	31/01/2018	1
A	T_2	2	31/01/2018	1
A	T_3	1	02/03/2018	-
A	T_3	1	02/03/2018	-
A	T_4	1	15/10/2018	2
A	T_5	1	17/10/2018	2
B	T_6	1	21/01/2018	3
B	T_7	1	24/01/2018	3
B	T_8	1	27/01/2018	3

The grouping column is what i wish to achieve. Basically transactions are sorted in ascending transacted date and if they are within 30 days of each other, they will be grouped together. If not they are not grouped together. Currently i'm able to get the number of days between each transaction and used a for loop to achieve that. However i'm wondering if there is a more efficient way of doing this as this method takes too long when dealing with large number of transactions.

Hopefully someone here can give me advice, thanks.

topic Grouping transactions based on difference in dates in App Development

Grouping transactions based on difference in dates