Change value of field during AutoGenerate

[#1]

Hello,

As part of a script I have the following lines:

WeekLink2011:

LOAD

     '2011'&rowno() as WEEKLINK2011

AutoGenerate(53);

WeekLink2012:

LOAD

     '2012'&rowno() as WEEKLINK2012

AutoGenerate(53);

WeekLink:

LOAD

    WEEKLINK2011 as WEEKLINK

Resident WeekLink2011;

LOAD

    WEEKLINK2012 as WEEKLINK

Resident WeekLink2012;

DROP Tables

      WeekLink2011,

      WeekLink2012;

This is working fine and generates a file that gives me 1 field that contains the value 20111, 20112, ..201153, 20121. 20122, ..201253.

So far so good.

But I try to learn the syntax and I am looking for the shorter way of doing this.

So I try something like:

for a = 2011 to 2012

Weeklink:

lOAD

   $(a) & RowNo()

AutoGenerate(53);

NEXT;

But the function row() counts above 53 till 106, so this doesn't work fo me.

So the question is, how to reset RowNo() to one again in the 2nd loop.

Or, and this is what I tried to achieve, what is the syntax for something like this: $(a) & (b=b+1) and reset b at the end of the loop.

Any help is welcome.

Regards,

Gerrit

[swuehl]

try using recno() instead of rowno().

In contrast to RecNo( ), which counts the records in the raw data table, the RowNo( ) function does not count records that are excluded by where clauses and is not reset when a raw data table is Concatenation to another.

[#3]

Swuehl,

That works fine.

And is surely a shorter way.

The result is two files which I then have to combine.

Fine solution, no problem.

But if I I would like to do this in one go?

Or, if for example I need to start not with 1 but with 100.

Is there something possible like:

Let b = 100;

for a = 2011 to 2012:

Weeklink:

LOAD

   $(a)& (b=+1)

AutoGenerate(53);

b=100;

NEXT;

Thanks in advance.

[MayilVahanan]

Hi'

You can use like this, recno()+100

Hope it helps

[swuehl · Accepted solution]

The result is two files which I then have to combine.

Fine solution, no problem.

But if I I would like to do this in one go?

Just give your fields the same name ( you don't really want to use these anyway,right?):

for a = 2011 to 2012

Weeklnk:

LOAD

   $(a) & RecNo() as WeeknrKey

AutoGenerate(53);

NEXT;

Thus your tables get auto concatenated.

And as already said, just add any number to recno() if you want.

Hope this helps,

Stefan

[#6]

Swuehl, yes this is the shortest way and that is what I tried to find.

Thanks to the posting of Mayil I was also able to find a solution using RowNo()

let b=0;

for a = 2011 to 2012

Weeklink:

load

  $(a) & (RowNo()-$(b)) as WeeknrKey

AutoGenerate(53);

b=53;

next;

Thanks both of you, I learned something today