iterno() and YearMonth

[Anonymous]

Hello,

I need some help to get the iterno() function to work the way I want. Here´s my table:

Field1      Date1       Date2

Value1     201201     201412

Value2     201301     202512

Value1     201401     202512

The Date fields have date format 'YYYYMM' in the source data file.

As you can see the same value can appear more than once. What I want to do is to create a control function, to highlight any values in Field1 that have overlapping periods. I´ve tried the iterno() function together with different combinations of date() and date#(), but sometimes it adds values like 201313, 201314 - 201399 and so on, and when I get it to stop at 201312 and start over with 201401 it tends to ignore my while statement. Since iterno() generates quite a lot of values I don´t want any values that are not needed.

Any ideas how to generate the values between the Date fields without getting any extra values?

//Johan

[swuehl · Accepted solution]

Try using real dates for the month adding:

LOAD *, num#(Date(addmonths(date#(Date1,'YYYYMM'),iterno()),'YYYYMM' )) as Date INLINE [

Field1,      Date1,       Date2

Value1,     201201,     201412

Value2,     201301,     202512

Value1,     201401,     202512

]

while addmonths(date#(Date1,'YYYYMM'),iterno()) <= date#(Date2,'YYYYMM');

[Anonymous]

Thanks, works perfekt