Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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;