Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to QlikView and am using QlikView for Windows version 11. I am having difficulting subtracting 1 date/time from a second date/time. I am using a CSV file as my datasource. Based on my abilty to concatenate the date/times, I believe the date/times are recognized as strings by Qlikview.
I have tried the timestamp#, date#, date functions but always get '-' for my answer. I have perused the QlikView Reference Manual and the QlikView Time and Date primer documents found during my google searches. I have tried converting the string into an actual date/time but have failed miserably.
Examples of my date/time data are: 3/20/2012 11:15 and 3/16/2012 8:47 (I have also uploaded my source file)
I have used the following formats to no avail:
(DATE([Logon time],'M/D/YYYY h:mm')) as LogonTimeDate
(DATE([Logon time],'M/D/YYYY hh:mm')) as LogonTimeDate
(DATE([Logon time],'M/D/YYYY hh:mm:ss')) as LogonTimeDate
(DATE([Logon time],'M/D/YYYY h:mm:ss')) as LogonTimeDate
(DATE([Logon time],'M-D-YYYY h:mm')) as LogonTimeDate
(DATE([Logon time],'M-D-YYYY hh:mm')) as LogonTimeDate
(DATE([Logon time],'M-D-YYYY hh:mm:ss')) as LogonTimeDate
(DATE([Logon time],'M-D-YYYY h:mm:ss')) as LogonTimeDate
Any guidance would be mucho appreciated!
Thanks,
John
Hi John,
try this and let me now what is the result
DATE(floor([Logon time]),'M/D/YYYY h:mm') as LogonTimeDate
Regards,
Chema
Hi Jose,
I had not tried the floor function. Unfortunately, I still get the same result the dash.
Thanks,
John
Hi John,
Try this
=Date(Date#('3/20/2012 11:15', 'M/DD/YYYY HH:mm'), 'MM/DD/YYYY HH:mm:ss')
Hope this helps you.
Regards,
Jagan.
Thanks Jagan, however it did not work for me. I have given up and converted by source file to an XLS and the timestamp# function works for me. I think the issue with my CSV was that is did not have the AM/PM included. This is simply a guess...
Many thanks for the help!