Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Create a timestamp from two string fields

Let me start by saying I'm using Qlik Sense. I have a date field and a time field. Both of these are stored as six character strings in the database. I want to take them and make them a single date/time field. Examples below.

DateTimeResult
05121501331305-12-2015 01:33:13
12131423324512-13-2014 23:32:45

I've tried something something like this to make it work but it's just now working out for me:

Timestamp#(Date#("date",'MMDDYY')&' '&Time#("time",'HHMMSS'), 'MM-DD-YYYY HH:MMSmiley FrustratedS)

The above expression gives me values that look like:

40461 205645 (10/10/2010 20:56:45)

40462 214058 (10/11/2010 21:40:58)

42136 163018 (5/12/2015 16:30:18)

What am I doing wrong???

I realize the number in place of the date is the number of days since the epoch but for whatever reason, it's not generating a number like 42136.687518

1 Solution

Accepted Solutions
Not applicable

Re: Create a timestamp from two string fields

Well. I had my head stuck up my back side for this one....

Turns out, my date field is a DateTime field coming in from the SQL server...not just a string.

So here's what I ended up with which (so far) appears to be working correctly:

timestamp#(date("date")&' '&"time", 'MM/DD/YYYY HHMMSS') as RecvDateTime

5 Replies
MVP
MVP

Re: Create a timestamp from two string fields

t:

LOAD Date,

     Time,

     Result,

     timestamp(Date#(Date, 'MMDDYY') + Time#(Time, 'hhmmss')) as New

FROM

[https://community.qlik.com/thread/163720]

(html, codepage is 1252, embedded labels, table is @1);

Not applicable

Re: Create a timestamp from two string fields

I changed my LOAD statement to this and now no data comes up after the load completes:

LOAD "date",

     "time",

     timestamp(date#("date",'MMDDYY') + time#("time",'hhmmss')) as RecvDateTime;

SQL...

MVP
MVP

Re: Create a timestamp from two string fields

Try this may be:

Table:

LOAD Date,

     Time,

     Result,

     Timestamp(timestamp#(Date&Time, 'MMDDYYhhmmss'), 'MM-DD-YYYY hh:mm:ss')  as New

FROM

[https://community.qlik.com/thread/163720]

(html, codepage is 1252, embedded labels, table is @1);

Not applicable

Re: Create a timestamp from two string fields

Well. I had my head stuck up my back side for this one....

Turns out, my date field is a DateTime field coming in from the SQL server...not just a string.

So here's what I ended up with which (so far) appears to be working correctly:

timestamp#(date("date")&' '&"time", 'MM/DD/YYYY HHMMSS') as RecvDateTime

MVP
MVP

Re: Create a timestamp from two string fields

Hahahaha, at least you found a solution.

Best,

Sunny