3 Replies Latest reply: Mar 16, 2016 3:14 AM by Stephane Chivet RSS

    Split Transactions into Period

    Stephane Chivet

      Hi,

      I need to split transactions into different Period (month) depending of the transaction date the starting and ending date. This has to be done during the load of the data (will be used in multiple places).

      Well the better way to explain is to show my input and what would be the output!

       

      Input:

      Id

      Date of Transaction

      Starting Date

      Ending Date

      Amount

      1

      2016-01-12

      2016-01-01

      2017-01-01

      12 000

      2

      2016-06-01

      2017-01-01

      2018-01-01

      12 000

      3

      2016-06-01

      2016-01-01

      2017-01-01

      12 000

      4

      2016-01-05

      2015-06-01

      2016-05-01

      11 000

      5

      2016-03-08

      2015-01-01

      2016-01-01

      68 765

       

      Output:

      In that case, when the Date of transaction is before or during the first period, it's just a split of the full period divided by the number of month

      Id

      Date of Transaction

      Period

       

      Amount

      1

      2016-01-12

      2016-01

       

      1 000

      1

      2016-01-12

      2016-02

       

      1 000

      1

      2016-01-12

      2016-03

       

      1 000

      1

      2016-01-12

      2016-04

       

      1 000

      1

      2016-01-12

      2016-05

       

      1 000

      1

      2016-01-12

      2016-06

       

      1 000

      1

      2016-01-12

      2016-07

       

      1 000

      1

      2016-01-12

      2016-08

       

      1 000

      1

      2016-01-12

      2016-09

       

      1 000

      1

      2016-01-12

      2016-10

       

      1 000

      1

      2016-01-12

      2016-11

       

      1 000

      1

      2016-01-12

      2016-12

       

      1 000

      same here:

      Id

      Date of Transaction

      Period

       

      Amount

      2

      2016-06-01

      2017-01

       

      1 000

      2

      2016-06-01

      2017-02

       

      1 000

      2

      2016-06-01

      2017-03

       

      1 000

      2

      2016-06-01

      2017-04

       

      1 000

      2

      2016-06-01

      2017-05

       

      1 000

      2

      2016-06-01

      2017-06

       

      1 000

      2

      2016-06-01

      2017-07

       

      1 000

      2

      2016-06-01

      2017-08

       

      1 000

      2

      2016-06-01

      2017-09

       

      1 000

      2

      2016-06-01

      2017-10

       

      1 000

      2

      2016-06-01

      2017-11

       

      1 000

      2

      2016-06-01

      2017-12

       

      1 000

       

      A bit more complicated, the date of transaction is after the start of the period but before the end:

      Id

      Date of Transaction

      Period

       

      Amount

      3

      2016-06-01

      2016-06

       

      6 000

      3

      2016-06-01

      2016-07

       

      1 000

      3

      2016-06-01

      2016-08

       

      1 000

      3

      2016-06-01

      2016-09

       

      1 000

      3

      2016-06-01

      2016-10

       

      1 000

      3

      2016-06-01

      2016-11

       

      1 000

      3

      2016-06-01

      2016-12

       

      1 000

      of course the full period can be less than 12 months:

      Id

      Date of Transaction

      Period

       

      Amount

      4

      2016-01-05

      2016-01

       

      8 000

      4

      2016-01-05

      2016-02

       

      1 000

      4

      2016-01-05

      2016-03

       

      1 000

      4

      2016-01-05

      2016-04

       

      1 000

       

      in that case, easy, the period is before the date of transaction:

      Id

      Date of Transaction

      Period

       

      Amount

      5

      2016-03-08

      2016-03

       

      68 765

       

      I know that I will multiply the number of lines but that's the requirement

       

      Anyone got an idea.

      Thanks a lot

        • Re: Split Transactions into Period
          Sunny Talwar

          May be something like this:

           

          Table:

          LOAD Id,

              [Date of Transaction],

              [Starting Date],

              [Ending Date],

              Amount/Count1 as Amount,

              Amount as Amount1,

              Count;

          LOAD Id,

              [Date of Transaction],

              [Starting Date],

              [Ending Date],

              ((Year([Ending Date])*12) + Month([Ending Date])) - (((Year([Starting Date])*12)+Month([Starting Date]))) as Count1,

              ((Year([Date of Transaction])*12) + Month([Date of Transaction])) - (((Year([Starting Date])*12)+Month([Starting Date]))) + 1 as Count,

              Amount;

          LOAD * Inline [

          Id, Date of Transaction, Starting Date, Ending Date, Amount

          1, 2016-01-12, 2016-01-01, 2017-01-01, 12000

          2, 2017-06-01, 2017-01-01, 2018-01-01, 12000

          3, 2016-06-01, 2016-01-01, 2017-01-01, 12000

          4, 2016-01-05, 2015-06-01, 2016-05-01, 11000

          5, 2016-03-08, 2015-01-01, 2016-01-01, 68765

          ];

           

          NewTable:

          LOAD Id as ID,

            [Date of Transaction],

            Period,

            If(Floor(MonthStart([Date of Transaction])) = Floor(Period), Amount * Count, Amount) as Amount

          Where Floor(MonthStart([Date of Transaction])) <= Floor(Period);

          LOAD Id,

            [Date of Transaction],

            Amount,

            Count,

            Date(MonthStart([Starting Date], IterNo() - 1), 'YYYY-MM') as Period

          Resident Table

          While AddMonths([Starting Date], IterNo() - 1) < [Ending Date];

           

          Concatenate(NewTable)

          LOAD Id as ID,

            [Date of Transaction],

            Date([Date of Transaction], 'YYYY-MM') as Period,

            Amount1 as Amount

          Resident Table

          Where Not Exists(ID, Id);

           

          DROP Tables Table;


          Capture.PNG

          • Re: Split Transactions into Period
            Gysbert Wassenaar

            Maybe like this:

             

            Data:
            LOAD
              *,
              if([Date of Transaction]> [Ending Date], Amount,
              if(MonthStart([Date of Transaction])<=[Starting Date],Amount/Months,
              if([Date of Transaction]>=Date, 
              Amount*(1-Delta +Delta/Months),
              Amount*(Delta)/Months
              )
              )
              ) as New_Amount
              ;
            LOAD 
              *, 
              MonthStart([Ending Date],-IterNo()) as Date
            WHILE
              Iterno() <= Months 
              ;
            LOAD 
              Id, 
              [Date of Transaction], 
              [Starting Date], 
              [Ending Date], 
              if([Date of Transaction]> [Ending Date], 
              1,
              if(MonthStart([Date of Transaction])<=[Starting Date],
              round(([Ending Date]-[Starting Date])/30) ,
              round(([Ending Date]-MonthStart([Date of Transaction]))/30)
              )
              )  as Months, 
              if([Date of Transaction]> [Ending Date], 
              1,
              if(MonthStart([Date of Transaction])<=[Starting Date],
              1/round(([Ending Date]-[Starting Date])/30),
              round(([Ending Date]-MonthStart([Date of Transaction]))/30) / round(([Ending Date]-[Starting Date])/30)
              )
              )  as Delta,
                 Amount
            INLINE [
                Id, Date of Transaction, Starting Date, Ending Date, Amount
                1, 2016-01-12, 2016-01-01, 2017-01-01, 12000
                2, 2016-06-01, 2017-01-01, 2018-01-01, 12000
                3, 2016-06-01, 2016-01-01, 2017-01-01, 12000
                4, 2016-01-05, 2015-06-01, 2016-05-01, 11000
                5, 2016-03-08, 2015-01-01, 2016-01-01, 68765
            ];
            
            • Re: Split Transactions into Period
              Stephane Chivet

              Thank you Sunny and Gysbert! Both solution are working perfectly!

              I will vote for you Sunny as you were the first one to answer but once again thank you Gysbert...

              Now that I've been working with real figures, it turned out that the requirement is a bit different! We need to apply the weight of the days within the months. In the case of Id 1, the result would be:

               

               

                 

              IdDate Of TransactionPeriodAmountShould be
              12015-01-012015-011 000,001 019,18
              12015-01-012015-021 000,00920,55
              12015-01-012015-031 000,001 019,18
              12015-01-012015-041 000,00986,30
              12015-01-012015-051 000,001 019,18
              12015-01-012015-061 000,00986,30
              12015-01-012015-071 000,001 019,18
              12015-01-012015-081 000,001 019,18
              12015-01-012015-091 000,00986,30
              12015-01-012015-101 000,001 019,18
              12015-01-012015-111 000,00986,30
              12015-01-012015-121 000,001 019,18
              Total 12 000,0012 000,00

               

               

              Any idea?