topic Re: Loop to create datefield by adding 2 months to each startdate until reach start + 24 month in QlikView

Loop to create datefield by adding 2 months to each startdate until reach start + 24 month

Wed, 11 Jun 2014 12:03:58 GMT

I have a table with two fields. ID and startdate. Each ID has a startdate, now I want to create a new datefield that should be generated by each ID´s startdate + 2month until it reach startate+24 months. I´m thinking of some sort of loop for each ID and startdate but have no clue how to start

The new datefield should look like this

First date is the startdate. ie 2011-02-01

2nd date is startdate +2 ie 2011-04-01

3d date is startdate +4 ie 2011-06-01

4th date is startdate + 6 ie 2011-08-01

5th date is startdate + 8 and so on until startdate + 24month/ 2 years are reached. ie2013-02-01

And that should be done fort each unic ID and it´s startdate.

Any suggestions are appreciated

Re: Loop to create datefield by adding 2 months to each startdate until reach start + 24 month

Gysbert_Wassenaar — Wed, 11 Jun 2014 12:16:36 GMT

let vStart = num(date#('2011-02-01','YYYY-MM-DD');

Dates:

load addmonths($(vStart),recno()*2 - 2) as StartDate

autogenerate(13);

Re: Loop to create datefield by adding 2 months to each startdate until reach start + 24 month

Wed, 11 Jun 2014 12:22:42 GMT

yes. but vStart have to be picked from the datefield 'startdate' beacuse it´s diffrent for each ID so the generated dates will be diffrent for each ID

Re: Loop to create datefield by adding 2 months to each startdate until reach start + 24 month

Gysbert_Wassenaar — Wed, 11 Jun 2014 12:27:23 GMT

No problem, but if you don't give us that information we'll instead solve the problem you did post. Please post a sample document with the real problem explained.

You'll probably need to use a while clause and the iterno() function like in the date example in this blog post: Loops in the Script

Re: Loop to create datefield by adding 2 months to each startdate until reach start + 24 month

Wed, 11 Jun 2014 12:37:23 GMT

Thanks. I attached the qvd file in my first post. But I don´t understand why it add.xml to the filename. Remove that and you have a sample.

More background:

Each student (in the sampledata fieldname FLYKTNR) have the right to a scholarship for 2 years from their start. The scholarship are given every second month with the first on at the startdate. And I would like to generate thoose dates as paydates to forcast how many scholarships we have each month forward in time.

Re: Re: Loop to create datefield by adding 2 months to each startdate until reach start + 24 month

Gysbert_Wassenaar — Wed, 11 Jun 2014 12:42:14 GMT

Try

Data:

LOAD FLYKTNR,

addmonths(StartDate, IterNo()*2-2) as StartDate

FROM [AddMonths.qvd](qvd)

while iterno() <=13 ;

If you don't want the last startdate, i.e. startdate+24months then change <=13 to <13 or <=12

Re: Loop to create datefield by adding 2 months to each startdate until reach start + 24 month

Wed, 11 Jun 2014 13:18:12 GMT

Thanks!