topic Re: Creating a timestamp from time and date correct it. in QlikView

Creating a timestamp from time and date correct it.

qlikconsultant — Wed, 02 Jan 2013 13:26:26 GMT

I have in an excel a date and time in 2 fields. I want to correct the date to the next day if the time is betwean 00:00:00 and 00:04:00.

As first i try to create a timestamp.

my_data:

load

date(mydate),

time(mytime),

date(mydate) &time(mytime) as my_Time_timestamp

FROM

Data.xlsx;

But as result i get an empty fields for my_Time_timestamp. What did I wrong?

Re: Creating a timestamp from time and date correct it.

hic — Wed, 02 Jan 2013 13:48:34 GMT

There could be many things.

First, check if a Load mydate, mytime From Excel interprets the fields as numbers (which it should). If the field values are right-aligned, then they are probably correctly interpreted.

If not, you need to use the date#() and time#() functions.

Once they are correctly interpreted, you should create the timstamp using

timestamp(date#(mydate) + time#(mytime))

HIC

Re: Creating a timestamp from time and date correct it.

qlikconsultant — Fri, 04 Jan 2013 10:51:59 GMT

I used this solution:

if(time(mytime<'04:00:00'), date(mydate+ 1), date(mydate)) as my_Time_timestamp,

Re: Creating a timestamp from time and date correct it.

Anonymous — Mon, 01 Dec 2014 10:15:40 GMT

I usually use this solution but sometimes I need to force the date format as:

timestamp(date#(date(mydate)) + time#(time(mytime)))

Ag+