Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mwallman
Contributor

How do I fix this mix format of dates issue in a Date field?

Hello friends!

I have the following table:

Date
Value
11-Nov-2015330
15/01/2016400
16/01/2016500
21-Feb-2015600

How do I solve the mix format of dates in the Date field to a single consistent format of DD/MM/YYYY?

I have tried using Date, Date#, MakeDate functions but did not have much luck solving the problem. My attempts ended up excluding the string based dates.

How can I solve this?

2 Replies

Re: How do I fix this mix format of dates issue in a Date field?

You can try this with Alt and Date# functions

Date(Alt(Num(Date), Num(Date#(Date, 'DD-MMM-YYYY')), Num(Date#(Date, 'DD/MM/YYYY')))) as Date


Capture.PNG

MVP
MVP

Re: How do I fix this mix format of dates issue in a Date field?

Try

LOAD

    Date( Alt(Date#(DATEFIELD,'DD-MMM-YYYY'), Date#(DATEFIELD,'DD/MM/YYYY') ),'DD/MM/YYYY') AS DATEFIELD,

   ...

FROM ..