Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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');
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');
The table [AIR Import Data] is defined like this before:
Date(LEFT(RIGHT('$(File)',15),10),'DD/MM/YYYY') as [AIR Import Data].
I think using MAX() should work... Can you take a look once again please...
What exactly are you wanting to see?
Thanks a lot !
So it did work with Max?
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
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?
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