Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jamiemcc
Contributor III
Contributor III

Convert Text Field to Date

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

Labels (1)
5 Replies
RafaelBarrios
Partner - Specialist
Partner - Specialist

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.

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/InterpretationF...

 

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! 🙂

jamiemcc
Contributor III
Contributor III
Author

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,

jamiemcc_1-1667401995556.png

 

 

brunobertels
Master
Master

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

SunilChauhan
Champion
Champion

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

Sunil Chauhan
RafaelBarrios
Partner - Specialist
Partner - Specialist

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! 🙂