topic Re: Max value in big table in QlikView

Max value in big table

Thu, 19 Dec 2013 04:08:44 GMT

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?

Re: Max value in big table

jagan — Thu, 19 Dec 2013 05:29:43 GMT

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.

Re: Max value in big table

Anonymous — Thu, 19 Dec 2013 06:18:14 GMT

Check if the idfield have numeric data or the string.

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

Re: Max value in big table

Thu, 19 Dec 2013 07:06:47 GMT

It's numeric data - create with autonumber.

Re: Max value in big table

Gysbert_Wassenaar — Thu, 19 Dec 2013 07:18:17 GMT

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.

Re: Max value in big table

jagan — Thu, 19 Dec 2013 07:36:49 GMT

Hi,

You can also try

Data:

LOAD * INLINE [

Year

2010

2011

2012

2013

];

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

Regards,

Jagan.

Re: Max value in big table

Thu, 19 Dec 2013 08:54:07 GMT

No, because value not unique. Thank's!

Re: Max value in big table

Thu, 19 Dec 2013 09:32:14 GMT

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?

Re: Max value in big table

Gysbert_Wassenaar — Thu, 19 Dec 2013 09:44:01 GMT

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.

Re: Max value in big table

jagan — Thu, 19 Dec 2013 09:46:13 GMT

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.

Re: Re: Max value in big table

Thu, 19 Dec 2013 09:50:44 GMT

I have in QVD file table with IdField, not order for this field.

I need add to this table new row - with generate for it new IdField as current max value idField + autonumber() as next id on new row.

AUTOGENERATE FieldValueCount('IdField') - it's not work for my table, because value in IdField like 1, 3, 5... and FieldValueCount('IdField') return 3 - but I want return 5.

Re: Max value in big table

Thu, 19 Dec 2013 09:52:52 GMT

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

But for this I reorder table in new table TableNew - and it all 40 second.

Re: Re: Re: Max value in big table

Gysbert_Wassenaar — Thu, 19 Dec 2013 10:01:47 GMT

FieldValueCount returns the unique number of values that the field IdField contains. It is used in combination with Autogenerate to cycle trough all the values in the IdField. By cycling through all the values it becomes possible to find the max value with the max(FieldValue('IdField', recno())) expression. So FieldValueCount is not the max IdField value, but that's not what it is used for anyway.

Re: Max value in big table

josevicentepla — Tue, 12 Jun 2018 19:33:14 GMT

This question is asked in QV12DA Certificarton Exam for Data Architect