topic Re: use for loop and enter multiple raws in QlikView

use for loop and enter multiple raws

anuradhaa — Thu, 05 Mar 2015 16:43:00 GMT

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.

Re: use for loop and enter multiple raws

MK_QSL — Thu, 05 Mar 2015 17:00:16 GMT

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;

Re: use for loop and enter multiple raws

maxgro — Thu, 05 Mar 2015 22:25:04 GMT

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;

Re: use for loop and enter multiple raws

rwunderlich — Fri, 06 Mar 2015 01:59:40 GMT

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

Re: use for loop and enter multiple raws

MK_QSL — Fri, 06 Mar 2015 07:58:31 GMT

Excellent pick !