Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following which qlik is interpreting as string
1/1/2016 1:00:18PM
1/1/2016 1:02:33PM
I need to reformat as MM/YY
=timestamp#(cr_timestamp,'M/D/YYYY h:mm:sstt') displays the same as the original
1/1/2016 1:00:18PM
1/1/2016 1:02:33PM
=date(timestamp#(cr_timestamp,'M/D/YYYY h:mm:ssTT'),'MM/YYYY') displays null.
The date# function works but as soon as I apply any formatting, it return null
There is one row which has "timestamp" in the field (including the double-quotes). Is this the problem?
Thanks to all for replies. They were quite helpful.
All the following 1/1/2016 which seems odd
LEFT('1/1/2016 5:00:00PM',10) should be 1/1/2016 5
LEFT('1/10/2016 5:00:00PM',10) ok
LEFT('10/10/2016 5:00:00PM',10) ok
Any # function would be ok but any formatting would result in null.
What worked was to use LEFT(field,10) for the date and RIGHT(field,10) for the time.
I've used datetime stamps before but suspect this had some non-display characters.
Hi,
=Date(Date#('1/1/2016 1:00:18PM','DD/MM/YYYY h:mm:sstt'),'MM/YYYY')
Your constant expression works but this is a field in the table.
Still getting null.
Try this script, it generates right format in front end -
Load Date(floor(Timestamp#(Date,'M/D/YYYY h:mm:sstt')),'MM/YYYY') as Date inline [
Date
1/1/2016 1:00:18PM
1/1/2016 1:02:33PM
"2/1/2016 1:02:33PM" ];
Try this as well
1) MonthName(Timestamp#('1/1/2016 1:00:18PM','D/M/YYYY hh:mm:ssTT'))
2) Date(Timestamp#('1/1/2016 1:00:18PM','D/M/YYYY hh:mm:ssTT'),'MM/YYYY')
Hi, how are you?
Could you try this?
=Date(
Date#(DateTimeField,'DD/MM/YYYY h:mm:sstt')
,'MM/YYYY')
See you latter,
Christyan Almeida
Thanks to all for replies. They were quite helpful.
All the following 1/1/2016 which seems odd
LEFT('1/1/2016 5:00:00PM',10) should be 1/1/2016 5
LEFT('1/10/2016 5:00:00PM',10) ok
LEFT('10/10/2016 5:00:00PM',10) ok
Any # function would be ok but any formatting would result in null.
What worked was to use LEFT(field,10) for the date and RIGHT(field,10) for the time.
I've used datetime stamps before but suspect this had some non-display characters.