Get max date data

[viveksingh]

Hi every one,

i have a date field like below in qvd

DATE
31/08/2018 12:00:00 AM
30/09/2018 12:00:00 AM

‌

I want load only maximum data that has max date. i.e.., want to load only 30/09/2018 only.

[fvelascog72]

Hi.

Try with:

ABC:

LOAD

Date(Max(Date#(Left(Date, 10), 'DD/MM/YYYY'))) as Date

INLINE [

Date

31/08/2018 12:00:00 AM

30/09/2018 12:00:00 AM

];

LET sDATE = Peek('Date', 0 , 'ABC');

And use the variable in the where condition.

Where Date#(Left(Date, 10), 'DD/MM/YYYY') = sDate

Saludos

[viveksingh]

‌tried ... but no luck.

I converted into number and stored the max mumber in a variable.

where i used the variable it throws error

[fvelascog72]

HI,

Try with $(sDate)

Saludos

[viveksingh]

‌here it is wor. But is not working with my data.

I Have a qvd with many fields, one of the fileld is date. all the records have two set of data one is for old date and other is for new date. I have to load only data with latest date.

[sunilkumarqv]

try this

=MAX(NUM(DATE(FLOOR(Timestamp#(DATE)),'DD/MM/YYYY')))

or

=MAX(NUM(DATE(FLOOR(Timestamp#(trim(DATE))),'DD/MM/YYYY')))

[Anonymous]

Hi try this:

LOAD

Date(max(num(date(left(DATE,19))))) as DATE

FROM

YourTable.qvd (qvd)

GROUP BY

DATE;

[viveksingh]

‌not working. In qvd the date is in number format. I stored max in variable and whe I use the variable it is not working

[trdandamudi]

If you post a sample file with your required output then it is going to be very easy to help and you will get the answer very quick. So, If you can please post a sample file.

[andrei_delta]

Hi,

first of all, i think you are reading "DATE" field from a database and don't be fooled by the format because that field is not a timestamp. if you make the field as a numeric you'll see only integer values.

and try this for getting your max value:

[Table of facts]:

load *

from X;

Temp:

Load

num(max(Date)) as DataMax

Resident [Table of facts];

drop table [Table of facts];

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

hope it helps,

Andrei