0 Replies Latest reply: Mar 31, 2018 5:35 AM by nil nil RSS

    Grouping transactions based on difference in dates

    nil nil

      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 IDTransaction IDTransaction_ItemTransacted DateGrouping
      AT_1101/01/20181
      AT_1201/01/20181
      AT_2131/01/20181
      AT_2231/01/20181
      AT_3102/03/2018-
      AT_3102/03/2018-
      AT_4115/10/20182
      AT_5117/10/20182
      BT_6121/01/20183
      BT_7124/01/20183
      BT_8127/01/20183

       

      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.