Date Help Urgent !!!

[#1]

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

[#2]

hi ankur,

use date(DataDate) as DataDate in your script.

thanks

[#3]

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.

[#4]

hi ankur,

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

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

thanks

[#5 · Accepted solution]

Hi

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

[#6]

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.

[#7]

Hello Tauqueer,

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

Best regards,

Ankur

[#8]

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

Best regards,

Ankur