QlikView App Dev

[#1]

In my table 20 million row.

I need select max value for ID (then use this max value + autonumber() as next id).

Now this script:

MaxIDTable:

LOAD max(IdField) as maxIdField

RESIDENT MyTable;

But it is very slower. May be have another way for get max value field?

[jagan]

HI,

Max() is the best method, if not then sort the table by IDField and load it into qlikview and try using Peek().

Regards.

Jagan.

[gupta_n8]

Check if the idfield have numeric data or the string.

If string , then use maxstring and if numeic then use max

[#4]

It's numeric data - create with autonumber.

[Gysbert_Wassenaar]

This will be faster:

LOAD max(FieldValue('IdField', recno())) as MaxIdField

AUTOGENERATE FieldValueCount('IdField');

LET vMaxIdField = peek('MaxIdField')

If your table is already ordered by IdField you could also try

LET vMaxIdField = peek('MaxIdField','MyTable');

That would just retrieve the last value in the table without the need for calculating the max.

[jagan]

Hi,

You can also try

Data:

LOAD * INLINE [  

    Year

    2010

    2011

    2012

    2013

];

LET vValue1 = FieldValue('Year',  NoOfRows('Data'));

Regards,

Jagan.

[#7]

No, because value not unique. Thank's!

[#8]

It's intresting about ordered by IdField.

If I ordered table:

TableNew:

NoConcatenate

LOAD * RESIDENT MyTable

ORDER BY IdField DESC;

And then peek('IdField ',0,'TableNew'); = it's 40 second.

But:

MaxIDTable:

LOAD max(IdField) as maxIdField

RESIDENT MyTable;

And then peek('IdField ',0,'TableNew'); = it's 4 minute!!!!!

WHY?

[Gysbert_Wassenaar]

I don't understand what you're doing. If you are reordering your table then you don't need to also calculate the max. So why are you creating MaxIDTable if you're not going to use it?

Anyway, if the source table isn't already ordered by IdField then it's probably faster to use the max(fieldvalue(... method. On small tables it may not matter much, but the larger the table to longer it will take to reorder it.

[jagan]

Hi,

peek('IdField ',0,'TableNew'); = it's 40 second.  -- Here you are not loading entire table, you are just picking the value of last record

MaxIDTable:

LOAD max(IdField) as maxIdField

RESIDENT MyTable;   

peek('IdField ',0,'TableNew'); = it's 4 minute!!!!!---  Here you are reloading the table and then getting the max value, so for reloading it is taking time

Hope this helps you.

Regards,

Jagan.