Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pauldamen
Partner
Partner

Text date as date

All,

I have a date field coming in from excel in this format: 01-Feb-16, which is recognized as a text field instead off date.

I tried all kind op functions like num#, date#, with trims but I can't get it to work.

Any suggestions?

Regards, Paul

1 Solution

Accepted Solutions
MayilVahanan

HI

Try like this

Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField

Thanks & Regards,
Mayil Vahanan R

View solution in original post

10 Replies
Kush
MVP
MVP

Set the below variable in script


SET DateFormat ='DD-MMM-YY';

Note : Make sure that Excel Date format should exactly match with SET variable format

Now load the excel file, it will automatically convert to Date.

swuehl
MVP
MVP

Have you tried a format like

Date#( YourField, 'DD-MMM-YY') as NewDateField

?

anagharao
Creator II
Creator II

Hi Paul,

You could use DATE() function in the load script for this.

MayilVahanan

HI

Try like this

Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField

Thanks & Regards,
Mayil Vahanan R

View solution in original post

pauldamen
Partner
Partner
Author

Yep no luck

swuehl
MVP
MVP

Is your MonthNames variable set accordingly at the beginning of your script?

Set MonthNames = 'Jan;Feb;Mar,....';

If this doesn't help, could you upload a small sample of your Excel input records?

Not applicable

Try this :

Date(Date#( YourField, 'DD-MMM-YY') , 'DD-MMM-YY')as NewDateField


Date# will convert your date to text format and date will convert it back to date format.



swuehl
MVP
MVP

Try like this

Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField

Strange, why should adding a formatting function like Date() helps in interpreting the text as date?

As far as I can see, the format code is the same as suggested before.

edit: Or maybe we are actually not coping with pure text values in the input records?

MayilVahanan

Hi Stefan,

Am converting the text field values to date values with his date format.

Thanks & Regards,
Mayil Vahanan R