use for loop and enter multiple raws

[anuradhaa]

hi ,

i have a date field and i want to split it based on below logic.

startdate           end date

2015/01/15     2015/03/05

i want to split above record as

startdate            end date

2015/01/15         2015/01/31

2015/02/01          2015/02/28

2015/03/01          2015/03/05

i have lot of records and i need to split them as above and store in a table.

is it possible to do this in qlikview script.

[MK_QSL]

Temp:

Load * Inline

[

  StartDate, EndDate

  15/01/2015, 05/03/2015

];

NoConcatenate

Final:

Load

  Date(MonthStart(StartDate,IterNo()-1)) as StartDate,

  Date(IF(MonthEnd(MonthStart(StartDate,IterNo()-1)) < EndDate, MonthEnd(AddMonths(StartDate,IterNo()-1)), EndDate)) as EndDate

Resident Temp

While Date(MonthStart(StartDate,IterNo()-1)) <= EndDate;

Drop Table Temp;

[maxgro]

another option (for loop)

Temp:

Load * Inline

[

  StartDate, EndDate

  15/01/2015, 05/03/2017

];

let vMonthNum=(year(peek('EndDate'))-year(peek('StartDate')))*12 + month(peek('EndDate'))-month(peek('StartDate'))+1;

for i=0 to $(vMonthNum)-1

  if ($(i)=0) then

       Table: load StartDate as Start, date(MonthEnd(StartDate)) as End Resident Temp;

  ELSEIF ($(i)=($(vMonthNum)-1)) then

       load date(MonthStart(EndDate)) as Start, EndDate as End Resident Temp;

  ELSE

       load

          date(AddMonths(MonthStart(StartDate), $(i))) as Start,

          date(AddMonths(MonthEnd(StartDate), $(i))) as End          

       Resident Temp;

  ENDIF;

NEXT;

DROP Table Temp;

[rwunderlich · Accepted solution]

Good solution Manish. I think you may have missed that the first date may start mid-month. I would use  RangeMax() for that and use RangeMin for the MonthEnd,

Load

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

  Date(RangeMin(MonthEnd(StartDate,IterNo()-1), EndDate)) as EndDate

Resident Temp

While Date(MonthStart(StartDate,IterNo()-1)) <= EndDate;

-Rob

http://masterssummit.com

http://robwunderlich.com

[MK_QSL]

Excellent pick !