topic Max Date statement on Where clause in QlikView

Max Date statement on Where clause

Tue, 26 Jul 2016 17:20:01 GMT

Hi,

In Qv load script, how can we write the following statement with the clause to get the Max Date like the example below:

SELECT *

FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.Id

WHERE Table1.Date = (select max(T1.Date)

from Table1 T1

where Table1.id = T1.id)

Someone could advice a way to achieve this?

Many thanks

Paulo

Re: Max Date statement on Where clause

maheshkuttappa — Tue, 26 Jul 2016 17:32:38 GMT

try this

SELECT *

FROM Table1 A ,

Table2 B

(select max(T1.Date) from

Table1 T1) C

Where A.id=B.Id

And A.id=C.id

Re: Max Date statement on Where clause

Tue, 26 Jul 2016 17:33:30 GMT

Table1:

load max(Date) as Date,

from ... group by id;

//table2

inner join (Table1)

load *

from ...

Re: Max Date statement on Where clause

sunny_talwar — Tue, 26 Jul 2016 17:35:56 GMT

May be this:

Table:

LOAD Id,

Date

From ....

Inner Join (Table)

LOAD Id,

Max(Date) as Date

Resident Table

Group By Id;

Re: Max Date statement on Where clause

vishsaggi — Tue, 26 Jul 2016 17:48:58 GMT

Kind of a work around, let me know if this works ?

Table1:

SELECT *

FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.Id;

Temp:

LOAD max(Date1) AS MaxDate

Resident Table1;

LET vMaxDate = Date(Peek('MaxDate',0, 'Temp'), 'MM/DD/YYYY');

Drop Table Temp;

Final:

Load *

Resident Table1

Where Date = $(vMaxDate);