Split Row into multiples rows based on dates

[lfalmoguera]

Hi all,

I am trying to split one row into multiples ones depending dates and then assign a certain value to it.

Let me explain it with an example:

INPUT_TABLE:

admin	product	totalPrice	dateStart	dateEnd
1	A	15	15/01/2017	15/03/2017
2	B	20	15/01/2017	31/03/2017

and I want it to calculate and assign a certain price for every month (period) depending in the "lenght" of the period:

Desired output table:

admin	product	monthlyPrice	dateStart	dateEnd	period
1	A	3,75	15/01/2017	31/01/2017	201701
1	A	7	01/02/2017	28/02/2017	201702
1	A	3,75	01/03/2017	15/03/2017	201703
2	B	4	15/01/2017	31/01/2017	201701
2	B	8	01/02/2017	28/02/2017	201702
2	B	8	01/03/2017	31/03/2017	201703

Thanks a lot in advance for the help. Much appreciate it.

[sunny_talwar]

Something along these lines

Table:

LOAD admin,

  product,

  totalPrice,

  Diff,

  Date(RangeMax(dateStart, MonthStart(dateStart, IterNo()-1))) as dateStart,

  Date(RangeMax(dateStart, MonthStart(dateStart, IterNo())-1)) as dateEnd,

  Date(MonthStart(dateStart, IterNo()-1), 'YYYYMM') as period

While MonthStart(dateStart, IterNo()-1) <= MonthStart(dateEnd);

LOAD *,

  dateEnd - dateStart + 1 as Diff;

LOAD * INLINE [

    admin, product, totalPrice, dateStart, dateEnd

    1, A, 15, 15/01/2017, 15/03/2017

    2, B, 20, 15/01/2017, 31/03/2017

];

Not sure how to determine Monthly price as if you will have more than just 15th of a month, it will be difficult to determine the portion of the total? Daywise? or if it is always 15th, then half month wise?

[hector_munoz]

Hi Luis,

If the result you want to achieve is something like:

, try the attached app.

Regards,

H