topic Re: extract time and date from field qlikview in QlikView

extract time and date from field qlikview

capriconuser — Sat, 16 Nov 2024 01:54:43 GMT

i have this kind of data

Check_in

18/11/2019 10:32:01

18/11/2019 10:12:28

18/11/2019 09:32:57

18/11/2019 09:28:58

18/11/2019 09:28:53

now i want to extract date and time from this field

i.e.

date time

18/11/2019 10:32:01

i try this in script but this does not working

Time(Fromtime) as check_in

Re: extract time and date from field qlikview

tresB — Tue, 19 Nov 2019 06:55:30 GMT

Try like:

Load Date#(check_in, 'D/M/YYY hh:mm:ss') as check_in, Date(Date#(check_in, 'D/M/YYY hh:mm:ss')) as Date, Time(Date#(check_in, 'D/M/YYY hh:mm:ss'), 'hh:mm:ss') as Time Inline [ check_in 18/11/2019 10:32:01 18/11/2019 10:12:28 18/11/2019 09:32:57 18/11/2019 09:28:58 18/11/2019 09:28:53]

Re: extract time and date from field qlikview

capriconuser — Tue, 19 Nov 2019 08:56:20 GMT

but i unable to extract only time from "to_time" field .. kindly check attached file

@tresB

Re: extract time and date from field qlikview

tresB — Tue, 19 Nov 2019 09:00:08 GMT

That is because your to_time field has pm/am component additionally. Try like:

Time(Date#(ToTime, 'D/M/YYY hh:mm:ss TT'), 'hh:mm:ss') as Checkout,

Re: extract time and date from field qlikview

capriconuser — Tue, 19 Nov 2019 09:40:03 GMT

@tresB still did not get the time from "to_time" have you checked excel sheet and qlik file

when i try to format date on excel this shows like this. sames dates are converted and some are not

Re: extract time and date from field qlikview

tresB — Tue, 19 Nov 2019 10:03:47 GMT

I could get time from your excel using :

Time(ToTime,'hh:mm:ss') as Time,

Re: extract time and date from field qlikview

capriconuser — Tue, 19 Nov 2019 10:20:09 GMT

but i did not get it.. will you please share qlikview file

Re: extract time and date from field qlikview

tresB — Tue, 19 Nov 2019 10:31:02 GMT

I can't upload the qvw now. However, sharing the code and screenshot; qvw doesn't have more than this.

LOAD ToTime, Time(ToTime, 'hh:mm:ss')as NewTime, FromTime FROM [excell_timein_sheet.xlsx] (ooxml, embedded labels, table is Attendances_20191118_1253);

Update:

Well, one more important settings could be:

SET DateFormat='M/D/YYYY'; SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Re: extract time and date from field qlikview

Brett_Bleess — Tue, 03 Dec 2019 22:16:40 GMT

Did Amit's last post get you what you needed? If so, do not forget to return to the thread and use the Accept as Solution button on that post to give him credit for the help and let other Community Members know this worked. If you are still working on things, leave an update with what you still need.

Regards,
Brett