Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

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
krishnacbe
Partner - Specialist III
Partner - Specialist III

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);

krishnacbe
Partner - Specialist III
Partner - Specialist III

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

Nope....NOT WORKING

Anil_Babu_Samineni

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

Load

ID,

Date

From

[Soure]

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
sudeepkm
Specialist III
Specialist III

First check the date format stored in the variable and then

Try:

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

apthansh
Creator
Creator
Author

Nothing works....reload itself is failing !

vinieme12
Champion III
Champion III

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);

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
apthansh
Creator
Creator
Author

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

sangland2
Partner - Creator
Partner - Creator

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 ?