Find out max numerical value in a QlikView script

[#1]

I've just been using QV for a week, so forige an obvious question. I've tried googling for this information with not much success.

I'm trying to construct an incremental load for my fact table. The fact table contains a primary key which takes the form of a [double] numerical integer. The column name for the PK is RecNo.

I have managed to include a where statement into my load statement e.g. 'WHERE RecNo > $(MaxRecNo)'. Ths script works fine if I set the value for MaxRecNo e.g. Let MaxRecNo = 1;

How do I calcualte the maximum value of RecNo for the records already loaded into QV?

Thanks in advance.

[Gysbert_Wassenaar]

You can do it like this:

Temp:

LOAD max(RecNo) as MaxRecNo;

LOAD FieldValue('RecNo', recno()) as RecNo

AUTOGENERATE FieldValueCount('RecNo');

LET vMaxRecNo = peek('MaxRecNo');

drop Table Temp;

[#3]

Thanks for this. Unfortunately I get a script error.

Script line error:

Temp:

LOAD max(FieldValue('RecNo', recno())) as MaxRecNo AUTOGENERATE FieldValueCount('RecNo')

The Temp table is not created. I get an error when the script attempts to drop the table.

[martinpedersen]

Try something like this:

Data:
Load RecNo() as Recno AutoGenerate 100;


_TmpTable:
Load Max(Recno) as MaxRecNo resident Data; 


Let MaxRecNo = FieldValue('MaxRecNo', 1);
Drop Table _TmpTable;


Concatenate:
Load Recno where Recno> $(MaxRecNo);
Load RecNo() as Recno AutoGenerate 200;