Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
apthansh
Contributor

Date where clause in load script

HI I have script as below and I only want to pick one date ie whichever is latest so I gave max(Date) in where clause.

Load

ID,

Date

From

[Soure]

WHERE Date = Max(Date);

This is not working

My Date format is as follows ;   2015-12-01 00:00:00

Thanks Much

10 Replies
Partner
Partner

Re: Date where clause in load script

Hope below Script helps

LatestDate:
Load Max(Date) as LatestDate
From
[Soure]
WHERE Date = Max(Date);

Let vMaxDate= Peek(LatestDate,0,LatestDate);

ActualData:
Load
ID,
Date
From
[Soure]
WHERE Date =
$(vMaxDate);

Partner
Partner

Re: Date where clause in load script

Based on the formatting of the Date in source you need to format.

Try directly the script if it doesn't work format the date in the variable.

apthansh
Contributor

Re: Date where clause in load script

Nope....NOT WORKING

Re: Date where clause in load script

May Be try something like below and then see how this works

Load

ID,

Date

From

[Soure]

WHERE Date(Date) = Date(Max(Date));

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sudeepkm
Valued Contributor III

Re: Date where clause in load script

First check the date format stored in the variable and then

Try:

WHERE Date = '$(vMaxDate)'; // Quotes around the variable

apthansh
Contributor

Re: Date where clause in load script

Nothing works....reload itself is failing !

vinieme12
Esteemed Contributor II

Re: Date where clause in load script

Try Below:



LatestDate:
Load Max(Date) as LatestDate
From
[Soure]
WHERE Date = Max(Date);

Let vMaxDate= Floor(Peek('LatestDate',0,'LatestDate'));

ActualData:
Load
ID,
Date
From
[Soure]
WHERE Floor(Date) =
$(vMaxDate);

apthansh
Contributor

Re: Date where clause in load script

dont we need group by while doing max in load script ?

Partner
Partner

Re: Date where clause in load script

I had  a similar problem. It was necessary to Floor the date to match to the variable even though the data appears to be a date already.ie  WHERE Floor(Date) = $(vMaxDate); works.  Not sure why this was necessary ?