Split Transactions into Period

[schivet]

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

[sunny_talwar · Accepted solution]

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;

[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
];

[schivet]

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:

Id	Date Of Transaction	Period	Amount	Should be
1	2015-01-01	2015-01	1 000,00	1 019,18
1	2015-01-01	2015-02	1 000,00	920,55
1	2015-01-01	2015-03	1 000,00	1 019,18
1	2015-01-01	2015-04	1 000,00	986,30
1	2015-01-01	2015-05	1 000,00	1 019,18
1	2015-01-01	2015-06	1 000,00	986,30
1	2015-01-01	2015-07	1 000,00	1 019,18
1	2015-01-01	2015-08	1 000,00	1 019,18
1	2015-01-01	2015-09	1 000,00	986,30
1	2015-01-01	2015-10	1 000,00	1 019,18
1	2015-01-01	2015-11	1 000,00	986,30
1	2015-01-01	2015-12	1 000,00	1 019,18
Total			12 000,00	12 000,00

Any idea?