topic Get max date data in QlikView

Get max date data

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Get max date data

fvelascog72 — Fri, 05 Oct 2018 08:26:36 GMT

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

Re: Get max date data

viveksingh — Fri, 05 Oct 2018 09:02:08 GMT

‌tried ... but no luck.

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

where i used the variable it throws error

Re: Get max date data

fvelascog72 — Fri, 05 Oct 2018 09:11:21 GMT

HI,

Try with $(sDate)

Saludos

Re: Get max date data

viveksingh — Fri, 05 Oct 2018 09:26:07 GMT

‌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.

Re: Get max date data

sunilkumarqv — Fri, 05 Oct 2018 09:27:48 GMT

try this

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

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

Re: Get max date data

Anonymous — Fri, 05 Oct 2018 09:28:29 GMT

Hi try this:

LOAD

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

FROM

YourTable.qvd (qvd)

GROUP BY

DATE;

Re: Get max date data

viveksingh — Fri, 05 Oct 2018 09:45:13 GMT

‌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

Re: Get max date data

trdandamudi — Fri, 05 Oct 2018 11:28:03 GMT

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.

Re: Get max date data

andrei_delta — Fri, 05 Oct 2018 13:10:25 GMT

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

Re: Get max date data

viveksingh — Fri, 05 Oct 2018 13:52:37 GMT

Hi,

Below is sample data,

lets assume the data is in qvd,

Expecting:

I'm able to convert date into number and max value in variable, when I use it in where condtion. it is throwing error. Looksa like I'm missing some syntax kind of thing.

Re: Get max date data

viveksingh — Sun, 07 Oct 2018 00:29:29 GMT

please help on this

Re: Get max date data

nasirsaikh — Sun, 07 Oct 2018 07:15:56 GMT

Simple is below one

Last_Updated_Date:

Load Max(YourDate) as Upload_Date

From [lib://QVD_INT (toi-bi_administrator)/TableName.qvd](qvd);

Let vMax_Date = date(PEEK('Upload_Date', 0, 'Last_Updated_Date'), 'YYYY-MM-DD');

Drop Table Last_Updated_Date;

Re: Get max date data

sujan24s — Sun, 07 Oct 2018 08:44:37 GMT

Hi,

You Want Same Like This

Re: Get max date data

trdandamudi — Sun, 07 Oct 2018 13:08:00 GMT

Here is the code and the output:

Data:
Load * Inline [
Name,Dept,Sal,Date
ABC,IT,100,1/10/2018
ABC,IT,101,2/10/2018
DE,IT,150,1/10/2018
DE,IT,151,2/10/2018
JK,HR,80,1/10/2018
JK,HR,81,2/10/2018
];

Right join (Data)
Load Name,
FirstValue(Date) as Date
Resident Data
Group by Name
order by Date desc;

Hope this helps...