Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist
Specialist

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

View solution in original post

4 Replies
MayilVahanan

Hi

Try like this

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

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
CELAMBARASAN
Partner - Champion
Partner - Champion

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
Specialist
Specialist

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
Author

Thank you Mayil, Celambarasan and Swarup for helping me.

Both the above  solutions are excellent

Mallikarjun