Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yifan_wu
New Contributor

How to convert strings like 10:00am or 10am to timestamp in Qlik Sense?

Hi,

I have two fields,One is a datetime field that stores the date value and one is a varchar, that stores the time value. But since it is a varchar, there are values like 10am, 10:00am, 10:00 am and 10..Is there anyway I can convert this varchar value to a timestamp and the combine it with the date field? My ultimate goal is to have a field with the actual date time and one with the date time value in a different timezone based on state value.

Thank you!

Tags (2)
1 Solution

Accepted Solutions

Re: How to convert strings like 10:00am or 10am to timestamp in Qlik Sense?

You can try formatting the timestamp in multiple ways within the alt() function, which will choose whatever returns a numeric value.  So for the examples you gave:

LOAD * INLINE [

Time

10am

10:00am

10:00 am

10

];

You can use:

alt(

timestamp#(Time, 'hhTT'),

timestamp#(Time, 'hh:mmTT'),

timestamp#(Time, 'hh:mm TT'),

timestamp#(Time, 'hh')

)

This will convert the time to a numeric value, which you can then combine with your date field.

2 Replies

Re: How to convert strings like 10:00am or 10am to timestamp in Qlik Sense?

You can try formatting the timestamp in multiple ways within the alt() function, which will choose whatever returns a numeric value.  So for the examples you gave:

LOAD * INLINE [

Time

10am

10:00am

10:00 am

10

];

You can use:

alt(

timestamp#(Time, 'hhTT'),

timestamp#(Time, 'hh:mmTT'),

timestamp#(Time, 'hh:mm TT'),

timestamp#(Time, 'hh')

)

This will convert the time to a numeric value, which you can then combine with your date field.

yifan_wu
New Contributor

Re: How to convert strings like 10:00am or 10am to timestamp in Qlik Sense?

Thanks Nicole! I had to add a timestamp() before the alt to make it work