topic Split Row into multiples rows based on dates in QlikView

Split Row into multiples rows based on dates

lfalmoguera — Mon, 24 Apr 2017 15:57:09 GMT

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.

Re: Split Row into multiples rows based on dates

sunny_talwar — Mon, 24 Apr 2017 16:31:44 GMT

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?

Re: Split Row into multiples rows based on dates

hector_munoz — Tue, 25 Apr 2017 07:16:05 GMT

Hi Luis,

If the result you want to achieve is something like:

, try the attached app.

Regards,