topic Re: Date field contains values in different formats ? in QlikView

Date field contains values in different formats ?

Wed, 19 Sep 2012 19:18:41 GMT

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

Re: Date field contains values in different formats ?

MayilVahanan — Wed, 19 Sep 2012 19:22:41 GMT

Try like this

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

Hope it helps

Re: Date field contains values in different formats ?

CELAMBARASAN — Wed, 19 Sep 2012 19:27:21 GMT

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')))

Re: Date field contains values in different formats ?

swarup_malli — Wed, 19 Sep 2012 19:40:55 GMT

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

Re: Date field contains values in different formats ?

Wed, 19 Sep 2012 19:58:19 GMT

Thank you Mayil, Celambarasan and Swarup for helping me.

Both the above solutions are excellent

Mallikarjun