Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi
Try like this
Load *,Date(DateField,'MM/DD/YYYY') as DateField from tableName;
Hope it helps
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')))
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
Thank you Mayil, Celambarasan and Swarup for helping me.
Both the above solutions are excellent
Mallikarjun