get max date from qvd

[#1]

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

[Oleg_Troyansky]

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,

[#3]

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);

[#4]

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)

[#5]

Polmar,

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

[#6]

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

[Oleg_Troyansky · Accepted solution]

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!

[#8]

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

[#9]

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

[#10]

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);