Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
cfountain72
Creator
Creator

Convert string timestamp to real timestamp...

Hello,

Hope you can help me with this one. I am receiving a field that looks like a timestamp, but Qlik doesn't see it as such. I am able to convert it to a timestamp whenever the hour has two digits (10, 11, 12), but for everything else I get a null. This is the code I am using:   

Timestamp(Date(ORDER_TIME), 'MM/DD/YYYY h:mm TT') as COVID_19_ORDER_TIME_RT,

ORDER_TIME not a 'time'ORDER_TIME not a 'time'

I tried applying time(), but again, it only extracted the hour when it is 10, 11, or 12.

 Any suggestions?

Thanks in advance,

Chris

Labels (3)
1 Solution

Accepted Solutions
cfountain72
Creator
Creator
Author

Hi,

The field had a single space between date and timestamps for those with two-digit times (10, 11, 12), and two spaces for those with one-digit times (4, 5, 9, etc.). This seems to have made Qlik unable to interpret them as timestamps, so the field had to be altered to make it do so. It's not beautiful, but this is what I ended up using. If anyone has a simpler solution, let me know!

if(Len(subfield(ORDER_TIME, ' ', 2))>0, Timestamp(Date#(SUBFIELD(ORDER_TIME,' ',1),'MM/DD/YYYY') + time#(subfield(ORDER_TIME, ' ', 2), 'hh:mm TT'), 'MM/DD/YYYY hh:mm TT')
, Timestamp(Timestamp#(text(ORDER_TIME),'MM/DD/YYYY hh:mm TT'),'MM/DD/YYYY hh:mm TT')) as COVID_19_ORDER_TIME_RT,

Thanks for the help in putting this solution together.

Chris

View solution in original post

5 Replies
joseph_morales
Creator II
Creator II

Hi @cfountain72 ,

You can try with this expression:

Timestamp(Timestamp#(ORDER_TIME,'MM/DD/YYYY hh:mm TT'),'MM/DD/YYYY hh:mm TT') as COVID_19_ORDER_TIME_RT

 

Best Regards,
Joseph Morales
cfountain72
Creator
Creator
Author

Hi Joseph,

Thanks for the quick reply and the suggestion. However, when i tried that, the timestamps with two-digit hours converted fine, but the ones with one-digit hours, converted to nulls:

order time compare.jpg

 

 

 

Any other ideas? It looks like an example where it fails would be like: 3/11/2020 4:23 PM

Thanks again,

Chris

Digvijay_Singh

The expression suggested should work for both single value or double value of hour. I tried both and its working for me. One thing I suspect is the values which are not working appearing as text to me as they are not right aligned. Whats the source of your data?

You may try something like this if you feel its because of single value of hour is the reason - 

Timestamp(alt(Timestamp#(ORDER_TIME,'MM/DD/YYYY hh:mm TT'),Timestamp#(ORDER_TIME,'MM/DD/YYYY h:mm TT')),'MM/DD/YYYY hh:mm TT')

 

mayuringale25
Partner
Partner

Hi @cfountain72 

Use this expression

date(floor(Date(Timestamp#("ORDER_TIME",'M/DD/YYYY hh:mm tt'),'DD/MM/YYYY'))) as COVID_19_ORDER_TIME_RT

Thanks and Regards
Mayur Ingale
cfountain72
Creator
Creator
Author

Hi,

The field had a single space between date and timestamps for those with two-digit times (10, 11, 12), and two spaces for those with one-digit times (4, 5, 9, etc.). This seems to have made Qlik unable to interpret them as timestamps, so the field had to be altered to make it do so. It's not beautiful, but this is what I ended up using. If anyone has a simpler solution, let me know!

if(Len(subfield(ORDER_TIME, ' ', 2))>0, Timestamp(Date#(SUBFIELD(ORDER_TIME,' ',1),'MM/DD/YYYY') + time#(subfield(ORDER_TIME, ' ', 2), 'hh:mm TT'), 'MM/DD/YYYY hh:mm TT')
, Timestamp(Timestamp#(text(ORDER_TIME),'MM/DD/YYYY hh:mm TT'),'MM/DD/YYYY hh:mm TT')) as COVID_19_ORDER_TIME_RT,

Thanks for the help in putting this solution together.

Chris