topic Date Help Urgent !!! in QlikView

Date Help Urgent !!!

Fri, 04 Jun 2010 17:20:42 GMT

Hello,

I have got to convert the 'DataDate' field to Date. But some of the dates are as Text and some as dates. (Scroll Down the DataDate listbox in the attached qvw).

Please tell me how can i achieve this in QlikView. The source data i multiple .csv files.

I cannot make any changes to the .csv files i have to take care of this in qlikveiw.

Looking forward to some help. QVW & QVD attached.

Thanks !!!!

Date Help Urgent !!!

Fri, 04 Jun 2010 17:58:00 GMT

hi ankur,

use date(DataDate) as DataDate in your script.

thanks

Date Help Urgent !!!

Fri, 04 Jun 2010 18:22:03 GMT

Hi Tauqueer,

Thanks for your reply, but if i do use date(DataDate) as DataDate, then it will only give me 104,022 records which are the once that are in the date format to begin with and it will omit 305,478 records which are in the text format. Have put the count as part of the list box captions.

Please correct me if i am wrong as i am not a developer.

Thanks.

Date Help Urgent !!!

Sun, 06 Jun 2010 05:40:39 GMT

hi ankur,

try this in your script,it should work fine.also see the attached application.

date#(DataDate,'DD/MM/YYYY') as DataDate

thanks

Date Help Urgent !!!

Sun, 06 Jun 2010 08:39:23 GMT

Try

Date(Date#(DataDate,'YYYYMMDD')) as DataDate,

YYYYMMDD = your source formate. so if your text is YYMMDD

Date(Date#(DataDate,'YYMMDD')) as DataDate,

Hope this help you

Date Help Urgent !!!

Sun, 06 Jun 2010 12:13:53 GMT

Hi,

Apparently the field is in mixed format.

Keep the source field, create another field using the same source. Use the Date# function and then pass the condition "if(isnum(xx), xx, text(Datedate), create another field, this time a flag field, "if(not isnum(xx), 'check date format'). After reload, use the flag field to check for the additional functions to be passed to get the remaining field values as dates!

Hope this helps. Cheers.

Date Help Urgent !!!

Sun, 06 Jun 2010 12:44:42 GMT

Hello Tauqueer,

Thank you for your reply. The solution has worked for me

Best regards,

Ankur

Date Help Urgent !!!

Sun, 06 Jun 2010 12:48:23 GMT

Thank you Mown and Nagesh for taking time out to reply.

Best regards,

Ankur