unable to store date in QVD

[#1]

Hi

I am trying to do IncrementalLoad and want to store my MaxDate into a QVD file.

My orginial QVD with all the data has a field called CreateDate

CreateDate is like 2014-05-22 10:00:00

When i want to store this

I select Max(CreateDate) as CreateDate into a QVD

But when i load my QVD it is empty.

Like it doesnt know the format or something=?

[#2]

If i just load all the dates without a Max i can see the dates.

So why does it not work with max?

[tresesco]

Possibly a format issue. Max() would need a numeric field (proper date field actually holds a numeric value). When you load without max, how does the data look like?

[#4]

My CreateDate is like this YYYY-MM-DD hh:mm:ss

I try something like this:

MaxIncrementalDate:

LOAD Date#(Date(Max(CreateDate),'YYYY-MM-DD hh:mm:ss')) as MaxIncrementalDate

FROM

(qvd);

Store MaxIncrementalDate into 'D:\QlikviewLoad\QVD\MaxIncrementalLoadDate.qvd';

//

Date:

LOAD MaxIncrementalDate as MaxIncrementalDate

FROM

(qvd);

LET vMaxIncrementalLoadDate = peek('MaxIncrementalDate',0,'Date');

If i load without the max, i get all the dates and can see data. But i only want the Max

[#5]

MaxIncrementalDate:

LOAD CreateDate as MaxIncrementalDate

FROM

(qvd);

Store MaxIncrementalDate into 'D:\QlikviewLoad\QVD\MaxIncrementalLoadDate.qvd';

//

Date:

LOAD MaxIncrementalDate as MaxIncrementalDate

FROM

(qvd);

LET vMaxIncrementalLoadDate = peek('MaxIncrementalDate',0,'Date');

This is working, but then i get all the dates - If i use Date# it doesnt work.

[tresesco · Accepted solution]

Try like:

LOAD Max(Date#(CreateDate,'YYYY-MM-DD hh:mm:ss')) as MaxIncrementalDate

[#7]

Then it gives me the numeric value of the date.

But i need the string cause im selecting data from and SQL connection where my createdate is a date/string characther

[#8]

This version works!

MaxIncrementalDate:

LOAD Max(Date#(CreateDate,'YYYY-MM-DD hh:mm:ss')) as MaxIncrementalDate

FROM

(qvd);

Store MaxIncrementalDate into 'D:\QlikviewLoad\QVD\MaxIncrementalLoadDate.qvd';

//

Date:

LOAD Date(MaxIncrementalDate,'YYYY-MM-DD hh:mm:ss') as MaxIncrementalDate2

FROM

(qvd);

LET vMaxIncrementalLoadDate = peek('MaxIncrementalDate',0,'Date');

Thanks!

[rbecher]

LOAD Date(Max(CreateDate),'YYYY-MM-DD hh:mm:ss') as MaxIncrementalDate ?

[tresesco]

Wherever you store that numeric value can be converted to string like date using date(), like:

Date($(variable), 'YYYY-DD-MM')  , or for a field

Date(DateField, 'YYYY-DD-MM')   , or directly during the load, like

LOAD Date(Max(Date#(CreateDate,'YYYY-MM-DD hh:mm:ss')) , 'YYYY-DD-MM') as MaxIncrementalDate