Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
viveksingh
Creator III
Creator III

Get max date data

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.

14 Replies
fvelascog72
Partner - Specialist
Partner - Specialist

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
Creator III
Creator III
Author

‌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
Partner - Specialist
Partner - Specialist

HI,

Try with $(sDate)

Saludos

viveksingh
Creator III
Creator III
Author

‌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
Specialist II
Specialist II

try this


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


or


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

Anonymous
Not applicable

Hi try this:

LOAD

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

FROM

YourTable.qvd (qvd)

GROUP BY

DATE;

viveksingh
Creator III
Creator III
Author

‌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
Master II
Master II

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
Partner - Creator III
Partner - Creator III

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