topic Re: Change 00:00am to 00:00 AM in QlikView

Change 00:00am to 00:00 AM

djbloiss — Wed, 10 Oct 2018 19:43:05 GMT

I have data in a field that often someone typed in 00:00am instead of 00:00 AM or 00:00pm instead of 00:00 PM. How do I separate it so it always recognized as time?

Re: Change 00:00am to 00:00 AM

sergio0592 — Wed, 10 Oct 2018 19:46:43 GMT

Hi,

Try with : Timestamp(Timestamp#(Your_field, 'hh:mmTT'),'hh:mm TT')

Re: Change 00:00am to 00:00 AM

swuehl — Wed, 10 Oct 2018 19:54:33 GMT

You can parse different formats using the Alt() function:

LOAD Time(Alt(Time#(Time,'hh:mmTT'),Time#(Time,'hh:mm TT'),Time)) as Time,

Time as TimeOrig

INLINE [

Time

12:02am

12:03AM

12:04 pm

12:01 PM

];

Re: Change 00:00am to 00:00 AM

djbloiss — Wed, 10 Oct 2018 19:58:07 GMT

wouldn't I have to type out every time then?

Re: Change 00:00am to 00:00 AM

swuehl — Wed, 10 Oct 2018 20:06:26 GMT

Are you talking about the INLINE LOAD?

No, that's just for demonstration of the Alt() function, so you can copy & paste to your QVW and reload.

In your real app, your data source will not be an INLINE table, but a DB or file table using a FROM ... instead of the INLINE ...

Re: Change 00:00am to 00:00 AM

djbloiss — Wed, 10 Oct 2018 20:09:12 GMT

This worked great except sometimes the date is in the correct format of 00:00 AM, 00:00 PM, or even 00:00 with no AM or PM. That is removing them? What do I do to keep those in the field as well?

Re: Change 00:00am to 00:00 AM

djbloiss — Wed, 10 Oct 2018 20:09:52 GMT

awesome thank you