topic Re: Convert Text Field to Date in App Development

Convert Text Field to Date

jamiemcc — Tue, 01 Nov 2022 14:34:27 GMT

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

Re: Convert Text Field to Date

RafaelBarrios — Tue, 01 Nov 2022 16:24:42 GMT

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/InterpretationFunctions/interpretation-functions.htm

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

Re: Convert Text Field to Date

jamiemcc — Wed, 02 Nov 2022 15:13:29 GMT

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,

Re: Convert Text Field to Date

brunobertels — Wed, 02 Nov 2022 16:24:10 GMT

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

Re: Convert Text Field to Date

SunilChauhan — Thu, 03 Nov 2022 08:17:01 GMT

Try this in script

MakeTime(Subfield(Created,':',1),subfield(Subfield(Created,':',2),'.',1),subfield(Subfield(Created,':',2),'.',2)) as CreatedNum

MakeTime(Subfield(Created,':',1),subfield(Subfield(Created,':',2),'.',1),subfield(Subfield(Created,':',2),'.',2))

on Gui

Re: Convert Text Field to Date

RafaelBarrios — Thu, 03 Nov 2022 09:55:58 GMT

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