topic Re:get max date from qvd in QlikView

get max date from qvd

Tue, 13 Oct 2009 23:33:23 GMT

im been using peek -1 but its returnig the wrong value as im doing a concatenated load and the lastloaded value is not the max value.

[Code] select CreatedDate from table1 order by created date asc //Peek ('createddate', -1) would work here
Concatenate
select CreatedDate from table2 order by created date asc //Now same peek would fail as the max from table 2 may not be larger than the max of table 1

//Store to QVD
//Peek ('Createddate', -1) [\Code]

Can you peek the max created date from a QVD?

Colin R

get max date from qvd

Oleg_Troyansky — Tue, 13 Oct 2009 23:46:30 GMT

Colin,

peek(field, -1) doesn't return the maximum - it returns the value from the last row in the table (or in the field, if you omit the table name). When you load the field from a single table, ordered by date, the two values are identical - the last value of the date is also the maximum. If you load the field from two tables, the result might not be the same, depending on the specific dates in each table...

If you want to be sure, reload your data and calculate the max(Date), and only then peek it into a variable.

Using "order by" in a QVD load must force the load into a not optimized mode, which is really slow - you might even save time by loading the data "as is" and then aggregating in memory...

cheers,

get max date from qvd

Tue, 13 Oct 2009 23:47:14 GMT

I do not understand you very much, but maybe with this:


Dates:
LOAD * INLINE [
 Date
 10/10/2009
 11/10/2009
];

store Dates into Dates.ild;

MaxDate:
Load date(max(Date)) as MaxDate
From Dates.ild (QVD);

Re:get max date from qvd

Wed, 14 Oct 2009 00:16:53 GMT

oleg,polmar

You are both correct, I dont want to use order by on load and i understand that my last row of the concatenated tables may not be the max date! I like the look of polmars solution as Im attempting to get the max loaded date in the qvd for use in the where clause of an incremental load.

The ReloadTime function doesnt seem to be accurate enough for my purposes. I have data streaming into my source table at subsecond rates!

Colin R
(Im Learning that there is more to learn)

Re:get max date from qvd

Wed, 14 Oct 2009 00:31:55 GMT

Polmar,

Nice idea but it didnt work for me (it created Syn1 table) to join the Maxdate table to table1

get max date from qvd

Wed, 14 Oct 2009 00:38:29 GMT

Oleg,

Back to you please:

You Quoted "If you want to be sure, reload your data and calculate the max(CreatedDate), and only then peek it into a variable."

Im new to Qv can you explain how to do this in the load script I tried earlier and got a error that i couldnt use max()

To be clear.

Im storing Initial Data Load into a QVD aliasing Field Names
Then Load * from QVD.
Then id Like to get max(CreatedDate) from Loaded QVD
Then Concatenate Load Where CreatedDate > max(CreatedDate)

Cheers
Colin R

get max date from qvd

Oleg_Troyansky — Wed, 14 Oct 2009 02:59:18 GMT

Colin,

you should use something like the following:


Tab1:
load * from myfile.qvd (qvd);

Tab2:
load
 max(CreatedDate) as MaxCreatedDate
resident Tab1
;
let v_MaxDate = peek('MaxCreatedDate');

Notice that the function max() will return a number (not a date). In order to present the result later on, you'll need to format it using function date(field, 'format')

cheers!

get max date from qvd

Wed, 14 Oct 2009 16:59:05 GMT

Cheers oleg, thanks for your time.

Its going to take a while to get up to speed with this product. Does the resident load have any Perfromance impacts . the Source QVD's are going to Get Huge (5 million rows for October so far) in Weekly QVD's (still need to work out how to load them as a collection)

I have completed the online tutorial and have 3 days of "in house" training booked. What would you suggest as the topics for this training as im supposed to be the "QlikVeiw guy" on its completion. the course content is up to me. Obviously i Want maximum value from this time.

Colin R

Colin

get max date from qvd

Wed, 14 Oct 2009 18:22:12 GMT

It created a new table and a syn table(s),I presume because the table structure had changed between the qvd load the maxdate date field and the Next concatenate.

I needed a drop field once id peeked the variable,

the Calc of MaxDate over an initial 5m rows took a while though ..

Colin R

get max date from qvd

Thu, 15 Oct 2009 18:11:33 GMT

Guys

Im already up to 2 million rows in the resident table and this block of code to get max (created date) is taking just short of 30 Seconds to get!

Calculating it before an incremental load and updating it after is taking almost a minute ! can you suggest a faster method ?

perhaps saving the variable Lastupdated into a file ?

Cheers
ColinR


trace Updating Last loaded Date;
ADD load max(CreatedDate) as MaxCreatedDate resident stkTRN;
let LastLoad = Date(peek('MaxCreatedDate'),'YYYYMMDD hh:mm:ss');
add drop field MaxCreatedDate;
trace Last Date Loaded $(LastLoad);

get max date from qvd

blaise — Thu, 15 Oct 2009 19:38:44 GMT

depending of the structure/setup of the database you might gain speed by doing the max(CreatedDate) directly from SQL. I assume you doing something like this;

Full Load (maybee on a weekly basis or similiar)

<code>

Complete:

Select * from dbo.Table1;

store Complete into data\complete.qvd;

drop table Complete;

MaxDate:

Select max(CreatedDate) as MaxDate from dbo.Table1;

store MaxDate into data\maxDate.qvd;

</code>

Next day, you do a incremental load... But before that, fetch the maxDate out of the maxDate.qvd.

<code>

MaxDate:

Load MaxDate from data\MaxDate.qvd;

Let vMaxDate = peek(MaxDate,-1);

drop table MaxDate;

Inc:

Select * from dbo.Table1 where CreatedDate >'$(vMaxDate)';

store Inc into Data\inc.qvd;

</code>

Now, concatenate the Complete and Inc qvd.

NB! To be sure to include all the data, you should consider doing a ...where CreatedDate>='$(vMaxDate)' and then use the "where not exists". This will slow down the performance, but if the db is updated 24/7 this might be the only way to go.

get max date from qvd

Thu, 15 Oct 2009 23:49:59 GMT

Unfortunatly the Source table has changed significantly between a Load completing and a seperate step query for maxdate from the DB. Im getting approx 500 rows per second for most of the day and its a 24/7 transactional table

Thanks for the idea of using seperate tables and concatenating using Where not exists, especially if i can do this with resident tables

get max date from qvd

Fri, 16 Oct 2009 00:09:58 GMT

I have an idea, can someone code it for me please. (it came from a modified version of blaises suggestion)

declare initial Startdate and endDate variables //for first load only
Store Startdate,EndDate to NextLoad.QVD
Load * from mydata.qvd where CreatedDate between StartDate and EndDate
//Update NextLoad.qvd
Store Old EndDate as Startdate,EndDate as now + 5 minutes overwriting NextLoad.QVD

This means that I wont need to Query Existing Data.QVD for the max Date as I will always now in advance what it should be

ColinR

Re: get max date from qvd

pgriffiths — Fri, 02 Oct 2015 13:08:19 GMT

This is super fast. Just reload the max of the field from the source QVD again.

A++

From the above example......

MaxDate:

Load date(max(Date)) as MaxDate

From Dates.ild (QVD);