Select the last Date in the field into the Script

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

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

[trdandamudi]

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

[sunny_talwar]

What exactly are you wanting to see?

[Anonymous]

Thanks a lot !

[sunny_talwar]

So it did work with Max?

[rwunderlich]

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

[sunny_talwar]

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?

[rwunderlich]

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