Convert string timestamp to real timestamp...

[cfountain72]

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'

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

 Any suggestions?

Thanks in advance,

Chris

[joseph_morales]

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

[cfountain72]

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:

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]

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

[cfountain72 · Accepted solution]

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