topic CSV DateTime Stamp not being handled properly in QlikView

CSV DateTime Stamp not being handled properly

hrish — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: CSV DateTime Stamp not being handled properly

ramasaisaksoft — Thu, 25 Jul 2019 07:50:32 GMT

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

Re: CSV DateTime Stamp not being handled properly

rish — Thu, 25 Jul 2019 08:04:15 GMT

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.

Re: CSV DateTime Stamp not being handled properly

Brett_Bleess — Mon, 19 Aug 2019 20:33:58 GMT

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