Date where clause in load script

[apthansh]

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

[krishnacbe]

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]

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]

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

[sudeepkm]

First check the date format stored in the variable and then

Try:

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

[apthansh]

Nothing works....reload itself is failing !

[vinieme12]

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]

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

[sangland2]

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 ?