Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max value in big table

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?

13 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

Check if the idfield have numeric data or the string.

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

Not applicable
Author

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.


talk is cheap, supply exceeds demand
jagan
Luminary Alumni
Luminary Alumni

Hi,

You can also try

Data:

LOAD * INLINE [  

    Year

    2010

    2011

    2012

    2013

];

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

Regards,

Jagan.

Not applicable
Author

No, because value not unique. Thank's!

Not applicable
Author

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.


talk is cheap, supply exceeds demand
jagan
Luminary Alumni
Luminary Alumni

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.