Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]
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,
@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
I could get time from your excel using :
Time(ToTime,'hh:mm:ss') as Time,
but i did not get it.. will you please share qlikview file
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';
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