Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hrish
Contributor
Contributor

CSV DateTime Stamp not being handled properly

Hi,

I have a csv in which I have a datetime field. Some the values in this field are of 24 hour format mm/dd/yyyy hh:mm while some are in 12 hour format mm/dd/yyyy hh:mm AM/PM. When I load the data with data profiling enabled, the 24 hour format values are not shown in the data manager. When I turned data profiling off and tried loading the data, all the values showed up, but when I tried to calculate a new Dates Only field in the sheet, it didn't work. 

I'd really appreciate it if you could solve this issue. For convenience I have given the examples of both below:

'07/24/2019 13:13' Not detected

'07/24/2019 01:13' PM is detected

 

3 Replies
ramasaisaksoft

Hi Hrish,

if you don't want time for your calculation try to convert that column with the help of Date ()& Date#() functions

date#(string, dateformat) converts a string to a date, optionally using the supplied date format

date() formats a date to the default date format for your system, or optionally the supplied date format.

 

 For example:

date(today(),'YYYY_DD_MM') returns  2019_25_07

date(today(),'MM/DD/YY') returns  07/25/19

Combination of both:

date(date#('2019_25_07','YYYY_DD_MM'),'MM/DD/YYYY') returns 07/25/2019

if you need time also then follow like below

date(date#( '20141029122728' , 'YYYYMMDDhhmmss'), 'DD.MM.YYYY') as date  

time(date#( '20141029122728' , 'YYYYMMDDhhmmss'), hh:mm:ss) as time

rish
Contributor III
Contributor III

Hello Ramasaisoft,

 

The issue is the values in 24 hour format are not being detected in the Data Manager at all, and being displayed as null. 

Brett_Bleess
Former Employee
Former Employee

Hey Rish, I believe you likely are trying to address this in the QlikView script, correct?  What I believe you need here are two things, decision as to which time format you want the data to be in when it ends up in the QlikView app and once that is determined, logic in the script to check the format and either reformat or read as-is.  I am pretty certain the issue is the mixed values, we are going to need things in a consistent value state, and the only good way to do this is during the load from the source environment, but you could also potentially clean things up in the back-end as well, if you want to run a stored procedure or something to handle things, you could do that as well I suspect.  

I think the following Design Blob post may be helpful in this case too:

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

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.