Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
lfalmoguera
Contributor

Split Row into multiples rows based on dates

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:

   

adminproducttotalPricedateStartdateEnd
1A1515/01/201715/03/2017
2B2015/01/201731/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:

 

adminproductmonthlyPricedateStartdateEndperiod
1A3,7515/01/201731/01/2017201701
1A701/02/201728/02/2017201702
1A3,7501/03/201715/03/2017201703
2B415/01/201731/01/2017201701
2B801/02/201728/02/2017201702
2B801/03/201731/03/2017201703

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

Tags (2)
2 Replies
Highlighted

Re: Split Row into multiples rows based on dates

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?

Highlighted
hector_munoz
Valued Contributor

Re: Split Row into multiples rows based on dates

Hi Luis,

If the result you want to achieve is something like:

25-04-2017 9-14-10.png

, try the attached app.

Regards,

H