topic Re: Splitting Date String into Time and Date Fields in QlikView

Splitting Date String into Time and Date Fields

ismasani — Fri, 08 Nov 2019 09:16:37 GMT

I have a date string in excel field that I currently have labeled as "date WHEN STATUS WAS CHANGED" and looks like this:

09- AUG-2019 9:01:00 AM

How will I be able to split this date string into two fields:

09/08/2019 as "Date stamp"

9:01:00 AM as "Time Stamp"

Re: Splitting Date String into Time and Date Fields

StarinieriG — Fri, 08 Nov 2019 09:22:07 GMT

Hi,

maybe something like this

LOAD
Date(Date#(test,'DD-MMM-YYYY hh:mm:ss TT'),'DD/MM/YYYY') as date,
Time(Date#(test,'DD-MMM-YYYY hh:mm:ss TT'),'hh:mm:ss TT') as time
;
LOAD * INLINE [
test
09-AUG-2019 9:01:00 AM
];

Re: Splitting Date String into Time and Date Fields

tresB — Fri, 08 Nov 2019 09:27:03 GMT

Try like:

Load Date(Floor(Timestamp#(DateTime, 'DD-MMM-YYYY hh:mm:ss TT')), 'DD/MM/YYYY') as Date, Time(Frac(Timestamp#(DateTime, 'DD-MMM-YYYY hh:mm:ss TT'))) as Time ; Load * Inline [ DateTime 09-AUG-2019 9:01:00 AM]

Re: Splitting Date String into Time and Date Fields

Brett_Bleess — Wed, 13 Nov 2019 14:11:37 GMT

Ismaila, do not forget to return to the thread and use the Accept as Solution button on any posts that helped you solve your issue, as this gives credit to the poster and lets others know what worked for your issue. In addition to the two posts, I am going to give you a base URL to the Design Blog area, this area has hundreds of how-to posts that will likely come in very handy...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: Splitting Date String into Time and Date Fields

ismasani — Wed, 13 Nov 2019 14:22:03 GMT

I tried this but it didn't work

Re: Splitting Date String into Time and Date Fields

ismasani — Wed, 13 Nov 2019 14:23:03 GMT

I tried this but it didn't work

Re: Splitting Date String into Time and Date Fields

Brett_Bleess — Wed, 13 Nov 2019 14:29:52 GMT

It will likely help if you attach a sample file the folks can check to see what the actual syntax of things is in that file...

Regards,
Brett