topic Re: Find out max numerical value in a QlikView script in QlikView

Find out max numerical value in a QlikView script

Mon, 20 May 2013 17:08:37 GMT

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.

Re: Find out max numerical value in a QlikView script

Gysbert_Wassenaar — Mon, 20 May 2013 17:20:04 GMT

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;

Re: Find out max numerical value in a QlikView script

Tue, 21 May 2013 09:48:07 GMT

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.

Re: Re: Find out max numerical value in a QlikView script

martinpedersen — Wed, 14 May 2014 12:19:37 GMT

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;