
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using Iterno() to generate 1 value per month instead of per day
I was looking at the information at the below link for finding the number of effective policies at a date, but instead of knowing as of any date I am looking for month end. Basically, since Iterno creates a value for each policy on each day there is too much data. I would rather be able to look at the data from a month end standpoint. Is there away to change Date( FromDate + IterNo() – 1 ) as ReferenceDate for a monthly view of the data? IterNo would need to create one record per policy per month end date which would greatly reduce the generated table.
Creating Reference Dates for Intervals
Edit:
hic Henric, would you happen to know if this is possible?
- Tags:
- effective date
- iterno
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Should be like this
LOAD Date(MonthStart(FromDate, IterNo() – 1)) as ReferenceDate
FROM ...
While MonthStart(FromDate, IterNo() – 1) <= MonthStart(ToDate);

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
Date(MonthStart(FromDate, IterNo() – 1)) as ReferenceDate

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No, this is still generating well over 100 million rows. I'm currently trying to play around with a variation of this to see if it works though.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How does you While statement look like? If you have not changed the While statement, then the above change will have no impact in the number of rows generated

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Should be like this
LOAD Date(MonthStart(FromDate, IterNo() – 1)) as ReferenceDate
FROM ...
While MonthStart(FromDate, IterNo() – 1) <= MonthStart(ToDate);

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Currently it is While IterNo()<= PolExpDt-PolEffDt+1;
For some reason what you recommended is not recognized.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The functions name is IterNo() and not InterNo()

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not sure if it is exactly what I need yet but your suggestion greatly limited the rows created. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I guess you can keep this thread open until you are 100% certain of a solution you needed. or you can come back and open a new thread if you find any issues.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I also an issue with your code... move this parenthesis to all the way at the end....
