Autogenerate with a for. next.... (Loop)

[#1]

Hi All,

I just can't seem to work this out and would appreciate any assistance.

Here is my script:

Let vRamsNoList = peek('RamsNoList',0,TempRamsList);

DROP Table TempRamsList;

For Each a in $(vRamsNoList)

TempCalendar:

Load $(a) as RamsNo, Date(addmonths(Date#(($(vMaxLoadedDate)&'01'),'YYYYMMDD'),(RowNo()-1)*-1),'YYYYMM') as Period autogenerate(24);

NEXT;

Calendar:

NoConcatenate

LOAD RamsNo, date(Period,'YYYYMM') as Period

Resident TempCalendar;

drop table TempCalendar;

This is what it is returning:          

RamsNo	Period
111	201212
111	201211
111	201210
111	201209
111	201208
111	201207
111	201206
111	201205
111	201204
111	201203
111	201202
111	201201
222	201112
222	201111
222	201110
222	201109
222	201108
222	201107
222	201106
222	201105
222	201104
222	201103
222	201102
222	201101
333	201012
333	201011
333	201010
333	201009
333	201008
333	201007
333	201006
333	201005
333	201004
333	201003
333	201002
333	201001

This is what i want it to return:

RamsNo	Period
111	201212
111	201211
111	201210
111	201209
111	201208
111	201207
111	201206
111	201205
111	201204
111	201203
111	201202
111	201201
222	201212
222	201211
222	201210
222	201209
222	201208
222	201207
222	201206
222	201205
222	201204
222	201203
222	201202
222	201201
333	201212
333	201211
333	201210
333	201209
333	201208
333	201207
333	201206
333	201205
333	201204
333	201203
333	201202
333	201201

Please advise on what i'm doing wrong, i'm convinced it is something to do with aggregating the autogenerated field with the RamsNo field.

Thanks.

[#2]

I have attached an example.

Please assist.

Thanks

[hic · Accepted solution]

Since I do not have the qvd file, I cannot see what goes wrong. But just looking at your script, I can see one big simplification: Don't use a For Next loop. You can do the same thing inside a Load statement using a While loop on a preceding Load:

LOAD

          RamsNo,

          date(Period,'YYYYMM') as Period;

Load

          RamsNo,

          Date(addmonths(Date#(($(vMaxLoadedDate)&'01'),'YYYYMMDD'),(IterNo()-1)*-1),'YYYYMM') as Period

          While IterNo() <= 24;

Load

          RamsNo

          FROM [......qvd] (qvd)

          where Active = 1;

Here I have two preceding loads in front of the Load that loads the QVD. So the bottom Load is piped into the Load with the While loop, which creates 24 records per initial record. Finally the result is piped into the top Load statement.

HIC

[hic]

An additional point (which has nothing to do with your loop not working). You use

     Let vMaxLoadedDate = '200001';

and then

     addmonths(Date#(($(vMaxLoadedDate)&'01'),'YYYYMMDD'), ... )

I would instead use

     Let vMaxLoadedDate = Num(MakeDate(2000,01)) ;

and

     addmonths($(vMaxLoadedDate), ... )

Then vMaxLoadedDate will be an integer that you can use directly in date and time functions. No additional interpretation necessary.

HIC

[#5]

Thank you Henric,

I think my script was very over complicated:)

Much appreciated.