Taking long time when finding max value of field

[Anonymous]

Hi all,

I have a table and is having around 200 million records. In that I am trying to get a max value of date filed in  table in load script and it is taking almost more than 1 hour of time to get max value of that field(field is date). Is there any way to reduce that much of time.

Thanks in advance.

Kumar

[sunny_talwar]

See if this link helps: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook

[Anonymous]

Thanks Sunny, For sharing such a valuable information.

QVD-*/FieldValues:

LOAD * FROM (qvd);

LOAD max(FieldValue(‘Id’, recno()))

AUTOGENERATE FieldValueCount(‘Id’);

I am trying to get  AUTOGENERATE FieldValueCount(‘Id’);

But it is not coming AUTOGENERATE. Can you see that in the screen shot

[jonathandienst]

FieldValueCount('') and FieldValue() take a field name as parameter look up info for that field in the QV symbol table. It looks like you are trying to pass an expression into FieldValue which will not work.

[Anonymous]

Yes, Jonathan.

Exactly, I want to pass expression value. Is there any other way to get that one.

Kumar

[sunny_talwar]

Can you share the script for Inserts table as well?

[jonathandienst]

Any method not using FieldValue/FieldValueCount:

QVD :
LOADmax(Id) FROM (qvd);
QVD-DISTINCT-Field/Resident:
LOAD DISTINCT Id FROM (qvd);
LOADmax(Id) Resident table;
QVD-Field/Resident:
LOAD Id FROM (qvd);
LOAD max(Id) Resident table;

You can substitute an expression for Id an any of these...

[Anonymous]

Let Vqvdpath = 'D:\QlikView\SourceDocuments\QVD\';

Inserts:

LOAD * FROM

$(Vqvdpath)fact_Orders.QVD (qvd);

MinMaxDates:

LOAD

     Max(FieldValue('Floor(Timestamp#(updated_datetime, 'MM/DD/YYYY hh:mm:ss.fff')))',recno())) as MaxDate,

Resident Inserts;

[sunny_talwar]

May be try something like this:

Inserts:

LOAD *,

          Floor(TimeStamp#(updated_datetime, 'MM/DD/YYYY hh:mm:ss.fff') as DateField;

LOAD *

FROM $(Vqvdpath)fact_Orders.QVD (qvd);

MinMaxDates:

LOAD Max(FieldValue('DateField', RecNo()))

AUTOGENERATE FieldValueCount('DateField');

[Anonymous]

Once again Thanks Sunny, In script Resident Inserts not required  after below step.

MinMaxDates:

LOAD Max(FieldValue('DateField', RecNo()))

AUTOGENERATE FieldValueCount('DateField');