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.

Not applicable

Date field contains values in different formats ?

Hi,

   I have a date field , this field contains dates in the following format (YYYY-MM-DD, DD-MM-YYYY and DD/MM/YYYY ).

  I want to convert all of these values  to one format.

  Any ideas ?

Mallikarjun

1 Solution

Accepted Solutions
swarup_malli
Not applicable

Re: Date field contains values in different formats ?

Mallikarjun,

alt(date#(DateString, 'YYYY-MM-DD'), date#(DateString, 'DD-MM-YYYY'), date#(DateString, 'DD/MM/YYYY'), 'No Date Found') AS Date

The alt() function returns the first value passed to it that is numerical, as  date in QlikView is a dual value (part of which is a number) we can use it to return the first date#() format that manages to create a true date.

If the first date#() function manages to interpret the string as a true date, then it will be returned, if not then it will move on to the next, and so on. If no numerical value is found, the alt() function will return the last value in the parameter list and so in this example, if it can't interpret the string using any of the given formats then it will return the string "No Date Found".

Regards

Swarup

4 Replies
mayilvahanan
Not applicable

Re: Date field contains values in different formats ?

Hi

Try like this

Load *,Date(DateField,'MM/DD/YYYY') as DateField from tableName;

Hope it helps

celambarasan
Not applicable

Re: Date field contains values in different formats ?

try something like this

if(IsNull(Date(Date#(FieldName,'YYYY-MM-DD'))),if(IsNull(Date(Date#(FieldName,'YYYY-MM-DD'))),Date(Date#(FieldName,'DD/MM/YYYY'))),Date(Date#(FieldName,'YYYY-MM-DD')))

swarup_malli
Not applicable

Re: Date field contains values in different formats ?

Mallikarjun,

alt(date#(DateString, 'YYYY-MM-DD'), date#(DateString, 'DD-MM-YYYY'), date#(DateString, 'DD/MM/YYYY'), 'No Date Found') AS Date

The alt() function returns the first value passed to it that is numerical, as  date in QlikView is a dual value (part of which is a number) we can use it to return the first date#() format that manages to create a true date.

If the first date#() function manages to interpret the string as a true date, then it will be returned, if not then it will move on to the next, and so on. If no numerical value is found, the alt() function will return the last value in the parameter list and so in this example, if it can't interpret the string using any of the given formats then it will return the string "No Date Found".

Regards

Swarup

Not applicable

Re: Date field contains values in different formats ?

Thank you Mayil, Celambarasan and Swarup for helping me.

Both the above  solutions are excellent

Mallikarjun