Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

extract time and date from field qlikview

hi 

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 

Labels (1)
8 Replies
tresesco
MVP
MVP

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]

2.PNG

capriconuser
Creator
Creator
Author

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

 

@tresesco 

 

 
 
tresesco
MVP
MVP

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,

capriconuser
Creator
Creator
Author

@tresesco  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

1.JPG

tresesco
MVP
MVP

I could get time from your excel using :

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

capriconuser
Creator
Creator
Author

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

tresesco
MVP
MVP

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);

 

 

Capture.PNG

Update:

Well, one more important settings could be:

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

 

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.