Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm new to Qlik Sense and I'm struggling to split a date/time field into separate date and time fields. The date/time column in a csv file has the following data
date/time |
6 Jan 2022 14:33:52 UTC |
7 Jan 2022 12:17:21 UTC |
7 Jan 2022 13:39:24 UTC |
7 Jan 2022 13:40:23 UTC |
7 Jan 2022 14:22:31 UTC |
8 Jan 2022 15:51:51 UTC |
9 Jan 2022 06:15:16 UTC |
10 Jan 2022 10:17:09 UTC |
10 Jan 2022 10:22:35 UTC |
10 Jan 2022 10:23:51 UTC |
10 Jan 2022 10:25:41 UTC |
Is it possible to split the field and have a date field in the following format DD/MM/YYYY and another field with the time in hh:mm:ss?
Thanks
Paul
I'm not sure if the UTC mark could be directly fetched from the timestamp# converting - if not replacing it may the most simple approach. After a converting a round/split function would be needed to get real dates/times and not timestamps which look like dates/times. Therefore your expression may look like:
date(floor(timestamp#(replace([date/time],' UTC', ''),'DD MMM YYYY hh:mm:ss')),'DD/MM/YYYY')
time(frac(timestamp#(replace([date/time],' UTC', ''),'DD MMM YYYY hh:mm:ss')),'hh:mm:ss')
Hi, try this.
Date(Floor(Timestamp#(Left([date/time],20),'DD MMM YYYY hh:mm:ss')),'DD/MM/YYYY') as Date
Time(Frac(Timestamp#(Left([date/time],20),'DD MMM YYYY hh:mm:ss')),'hh:mm:ss') as Time
I'm not sure if the UTC mark could be directly fetched from the timestamp# converting - if not replacing it may the most simple approach. After a converting a round/split function would be needed to get real dates/times and not timestamps which look like dates/times. Therefore your expression may look like:
date(floor(timestamp#(replace([date/time],' UTC', ''),'DD MMM YYYY hh:mm:ss')),'DD/MM/YYYY')
time(frac(timestamp#(replace([date/time],' UTC', ''),'DD MMM YYYY hh:mm:ss')),'hh:mm:ss')
Thank you Marcus, your code worked perfectly.