Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Specialist II
Specialist II

Check if the idfield have numeric data or the string.

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

Highlighted
Not applicable

It's numeric data - create with autonumber.

Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

You can also try

Data:

LOAD * INLINE [  

    Year

    2010

    2011

    2012

    2013

];

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

Regards,

Jagan.

Highlighted
Not applicable

No, because value not unique. Thank's!

Highlighted
Not applicable

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?

Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
MVP & Luminary
MVP & Luminary

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.