Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following issue, I have to increase the date by 1 when the time is > 23:59:59.
For example:
01/08/2011 00:00:23 needs to be 02/08/2011 00:00:23
I have the following expression but this changes al the dates:
if(NotLiveTxStart > '23:59:59', NotLiveTxStart +1, NotLiveTxStart)
What is missing here to get ONLY the dates higher than 23:59:59 to be increased?
Thanks!
What you need to keep in mind is that the time is presented as the decimal part of a number, so if you are about to compare numbers you need to convert the values to be compared to similar values. The decimal value represents the fraction of 24 hours that have passed.
So if you are calculating seconds the decimal corresponds to ( (H * 60 * 60) + (M *60) * (S) ) / (24 * 60 * 60)
which for 23:59:59 will be (82800 + 3540 + 59) / 86400 = 86399 / 86400 = 0,99998843...
As you can see there is no value larger than this on the clock, as 24:00:00 equals 00:00:00 and the decimal 0,0
Either way you setup your logic you only need to keep the decimal presentation in mind, and use the Time() and Time#() functions to format your data if required in the expressions. If you have a timestamp then the time functions will basically remove the integer part of the data so that you get the decimal time presentation.
Not sure what your value NotLiveTxStart contains, but if it is a TimeStamp value then you will increase it's date part by a day when you make +1 since the date is the integer part of the numeric value in the TimeStamp. If this is your problem, the make sure that the field is loaded as a TimeStamp by applying TimeStamp() and/or TimeStamp#() in the script.
hei
attach is an example
hope its helps you
After all the best solution was to do this on database level. Qlikview uses a view to get the data, so I used a CASE statement there to solve this.
Thanks for the help!