Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have attached two different formats (Created Column) - One as text and other as date . Im trying to read into QLIK but I cant convert text field to date? I want to read both Columns as one into QLIK.
How do I combine and convert into date in QLIK?
Thanks,
Jamie
HI @jamiemcc
i cant see any date in you file, only a created time
in any case, you would have to use date#() and specify the format in which you are receiving the date.
lets say your app format is MM/DD/YYYY and in your data you have 2022-11-1
you can do this:
date#(date_field,'YYYY-MM-DD')
same thing for time (tt for am/pm)
time#(date_field,'hh:mm:ss tt')
and if you have date and time you can do this
2022-11-1 12:04:00 AM
date#(date_field,'YYYY-MM-DD hh:mm:ss tt')
hope this helps.
Best,
help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂
Thanks Rafael for your reply.
When I bring In the file to QLIK and try to treat it as a date Date(Created) it brings up null like attached.
Why isn't it treating it like a date?
TryRating:
LOAD Distinct *,
FileName() as FileName,
FileTime() as FileTime,
date#(Created,'YYYY-MM-DD') as Created1,
date#(Created,'YYYY-MM-DD hh:mm:ss tt') as Created2,
hi
try this
date(date#(left('2022-10-31 05:54:22.503600+00:00',10),'yyyy-mm-dd'),'yyyy-mm-dd')
replace '2022-10-31 05:54:22.503600+00:00' with your dimension Created
Try this in script
MakeTime(Subfield(Created,':',1),subfield(Subfield(Created,':',2),'.',1),subfield(Subfield(Created,':',2),'.',2)) as CreatedNum
or
MakeTime(Subfield(Created,':',1),subfield(Subfield(Created,':',2),'.',1),subfield(Subfield(Created,':',2),'.',2))
on Gui
hi @jamiemcc
as i think you wont need the timezone +00:00
you could try taking it out
subfield('2022-10-23 14:30:59:123456+00:00','+',1)
and then specify your timestamp format if Qlik doesnt do it automaticaly
from what i can see it should, but if not
timestamp#(subfield('2022-10-23 14:30:59:123456+00:00','+',1),'YYYY-MM-DD hh:mm:ss:fff')
for you would be
timestamp#(subfield(Created,'+',1),'YYYY-MM-DD hh:mm:ss:fff')
Hope this helps.
Best,
help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂