topic Re: Change value of field during AutoGenerate in QlikView

Change value of field during AutoGenerate

Sat, 04 Aug 2012 13:11:54 GMT

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

Re: Change value of field during AutoGenerate

swuehl — Sat, 04 Aug 2012 13:59:03 GMT

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.

Re: Change value of field during AutoGenerate

Sat, 04 Aug 2012 15:05:57 GMT

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.

Re: Change value of field during AutoGenerate

MayilVahanan — Sat, 04 Aug 2012 20:19:24 GMT

Hi'

You can use like this, recno()+100

Hope it helps

Re: Change value of field during AutoGenerate

swuehl — Sat, 04 Aug 2012 21:02:13 GMT

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

Re: Change value of field during AutoGenerate

Sat, 04 Aug 2012 21:16:52 GMT

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