topic Re: Optimized code to fetch the maximum date from a qvd in QlikView

Optimized code to fetch the maximum date from a qvd

anushahegde — Sat, 16 Nov 2024 02:13:59 GMT

Hi all,

I have a qvd containing 618,994,678 records. I did the optimized qvd load and now I want to fetch the maximum date from this qvd. what is the best way to do it so that it consumes less time to give the maximum date?

Thank you

Anusha

Re: Optimized code to fetch the maximum date from a qvd

viveksingh — Mon, 14 Oct 2019 06:13:42 GMT

Try Like below:

Filterdate:

Load
date(Max(DATE_COLUMN),'DD/MM/YYYY') as maxdate

from QVD;

SET vmaxdate=peek('maxdate', 0, 'Filterdate');

Re: Optimized code to fetch the maximum date from a qvd

anushahegde — Mon, 14 Oct 2019 07:38:12 GMT

Looks like Max(Date) will consume lot of time.

Re: Optimized code to fetch the maximum date from a qvd

andrei_delta — Mon, 14 Oct 2019 11:08:48 GMT

hi,

i use this code for my timeline

Temp:

LOAD num(floor(Max( [newdatefield] ))) as DataMax,
num(floor(Min( [newdatefield] ))) as DataMin;
LOAD FieldValue('yourdatefield', recno()) as [newdatefield]
AUTOGENERATE (FieldValueCount('yourdatefield'));

LET vDataMin = Peek('DataMin',-1,'Temp');
LET vDataMax = Peek('DataMax',-1,'Temp');

it's way faster than simple max() applied on the date field.

hope it helps,

Andrew

Re: Optimized code to fetch the maximum date from a qvd

Victor_Alumanah — Mon, 14 Oct 2019 11:42:53 GMT

temp:

LOAD DATE_COLUMN

FROM

[S00.QVD]

(qvd);

MinMaxDate:

Load min(DATE_COLUMN) as MinDate,

max(DATE_COLUMN) as MaxDate;

Load FieldValue('DATE_COLUMN',IterNo()) as DATE_COLUMN

autogenerate(1)

while not IsNull(FieldValue('DATE_COLUMN',Iterno()));

Drop Table temp;

Re: Optimized code to fetch the maximum date from a qvd

Brett_Bleess — Mon, 21 Oct 2019 19:07:24 GMT

Anusha, If the goal is to load the QVD file optimized, then do that into a Temp table as the last poster recommended and then run your addtional code against the Temp table and once you have what you need drop the Temp table from the data model. If Victor's post was what you used to solve things, please remember to give credit and let others know things worked by using the Accept as Solution button on the post(s) that helped you get to your solution. If you did something else, consider posting that and then mark it, and if you are still working on things, update the post with where you are with things.

Regards,
Brett

Re: Optimized code to fetch the maximum date from a qvd

JGMDataAnalysis — Mon, 21 Oct 2019 19:37:50 GMT

https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/

Temp: LOAD Max(Date) AS MaxDate FROM 'YourPath.qvd' (qvd); LET vMaxDate = Num(Peek('MaxDate', 0, 'Temp')); DROP TABLE Temp;