Discussion Board for collaboration on QlikView Scripting.
based on other discussions, I used the following expression to create a timestamp
based on a date and an hour from different fields.
Timestamp(RsvLineCreateDate+ [RsvLine Create Hour], 'DD/MM/YY hh:mm')
But the final date in the timestamp is always 2 days later then the basis date.
final timestamp: 09/11/2011 07:51
Any idea why it happens?
What (actual numeric) values do the two fields contain? It seems that one of them is not what you think.
can show the screenshot of RsvLineCreateDate, [RsvLine Create Hour] in list box?
It seems that the issue is in the time field:
The expression =num([RsvLine Create Hour]) yields:
so the 2 days are actually hidden in the time field..
Displaying the time field as timestamp shows:
How does this happen???
instead doing it using + operator, format it using Timestamp function like below
= Timestamp(Timestamp#(RsvLineCreateDate & [RsvLine Create Hour],'MM/DD/YYYYhh:ss'),'DD/MM/YYYY hh:mm')
I think you need to convert the fields into date and time internal values before you add them.
= timestamp(date#('07/11/2011','DD/MM/YYYY') +time#('07:51:00','hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')
I thought it would be " healthier" to do the following:
Timestamp(Num((RsvLineCreateDate)+num(MakeTime(Hour([RsvLine Create Hour])))),'DD/MM/YYYY hh:00')
Looks messy but re solved the issue... :-)
another possibility to get rid of the (integer) day part of your time field:
Timestamp(RsvLineCreateDate + Frac([RsvLine Create Hour]), 'DD/MM/YY hh:mm')
hope this helps