topic Date where clause in load script in QlikView

Date where clause in load script

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

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

Re: Date where clause in load script

krishnacbe — Wed, 11 Jan 2017 18:04:37 GMT

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

Re: Date where clause in load script

krishnacbe — Wed, 11 Jan 2017 18:06:03 GMT

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.

Re: Date where clause in load script

apthansh — Wed, 11 Jan 2017 18:14:23 GMT

Nope....NOT WORKING

Re: Date where clause in load script

Anil_Babu_Samineni — Wed, 11 Jan 2017 18:18:45 GMT

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

Load

ID,

Date

From

[Soure]

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

Re: Date where clause in load script

sudeepkm — Wed, 11 Jan 2017 18:19:35 GMT

First check the date format stored in the variable and then

Try:

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

Re: Date where clause in load script

apthansh — Wed, 11 Jan 2017 18:26:12 GMT

Nothing works....reload itself is failing !

Re: Date where clause in load script

vinieme12 — Wed, 11 Jan 2017 18:26:59 GMT

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

Re: Date where clause in load script

apthansh — Wed, 11 Jan 2017 18:26:59 GMT

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

Re: Date where clause in load script

sangland2 — Sat, 05 May 2018 00:11:59 GMT

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 ?

Re: Date where clause in load script

sasiparupudi1 — Sat, 05 May 2018 13:27:21 GMT

MaxDate:

Load

Max(date) as MaxDate

From

[Source]

;

Let vMaxDate=Date(Floor(Peek('MaxDate',0,'MaxDate')));

Load

ID,

Date

From

[Soure]

WHERE Date(Floor(Date))='$(vMaxdate)'

;