topic Select the last Date in the field into the Script in QlikView

Select the last Date in the field into the Script

Anonymous — Thu, 19 May 2016 16:37:37 GMT

Hello,

I would like to assimilate to the named 'Last_Update_NBI' the last Date present into the field [AIR Import Data], so i did this into the script:

load Distinct [AIR Import Data] as LastAsOf

resident [FinalTable];

Let Last_Update_NBI = Date(Peek('LastAsOf'), 'DD/MM/YYYY');

But it return that:

Where 31/12/2015 is considered as the last value by QlikView 😕

I tried to use the MAX() function but it doen't work.

How can i do that please ?

Thanks you in advance for your help,

Sébastien.

Re: Select the last Date in the field into the Script

sunny_talwar — Thu, 19 May 2016 16:39:50 GMT

So you are saying this did not work with Max (like below)?

LOAD Max([AIR Import Data]) as LastAsOf

Resident [FinalTable];

LET Last_Update_NBI = Date(Peek('LastAsOf'), 'DD/MM/YYYY');

Re: Select the last Date in the field into the Script

Anonymous — Thu, 19 May 2016 16:42:24 GMT

The table [AIR Import Data] is defined like this before:

Date(LEFT(RIGHT('$(File)',15),10),'DD/MM/YYYY') as [AIR Import Data].

Re: Select the last Date in the field into the Script

trdandamudi — Thu, 19 May 2016 16:42:47 GMT

I think using MAX() should work... Can you take a look once again please...

Re: Select the last Date in the field into the Script

sunny_talwar — Thu, 19 May 2016 16:45:10 GMT

What exactly are you wanting to see?

Re: Select the last Date in the field into the Script

Anonymous — Thu, 19 May 2016 16:52:04 GMT

Thanks a lot !

Re: Select the last Date in the field into the Script

sunny_talwar — Thu, 19 May 2016 16:53:16 GMT

So it did work with Max?

Re: Select the last Date in the field into the Script

rwunderlich — Thu, 19 May 2016 16:54:09 GMT

Your date was not loaded as a "proper" (numeric) date. You need to add a date#() function to tell QV how to parse the date.

Date(Date#(LEFT(RIGHT('$(File)',15),10),'DD/MM/YYYY'), 'DD/MM/YYYY') as [AIR Import Data]

After loading this way, max() should work. Without date#(), the values are considered strings and "31" is greater than "29".

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Select the last Date in the field into the Script

sunny_talwar — Thu, 19 May 2016 16:56:47 GMT

Rob correct me if I am wrong, but if the environmental variable use the following DateFormat = 'DD/MM/YYYY', won't the Date() function recognize the string as date?

Re: Select the last Date in the field into the Script

rwunderlich — Thu, 19 May 2016 17:14:34 GMT

Sunny,

Good point and that appears to be what's happening here. However, I don't think the date() function has anything to do with it. QV will recognize the string "DD/MM/YYYY" as a date because it matches the DateFormat variable.

So the date#() is not necessary, but neither is the date(). It should work with the shorter form:

LEFT(RIGHT('$(File)',15),10)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Select the last Date in the field into the Script

sunny_talwar — Thu, 19 May 2016 17:26:13 GMT

Agreed, unless another type of format is desired using the Date() function it won't make a difference

Re: Select the last Date in the field into the Script

Anonymous — Thu, 19 May 2016 20:59:05 GMT

Yes !

Re: Select the last Date in the field into the Script

sunny_talwar — Thu, 19 May 2016 21:00:37 GMT

Great