topic Re: Text date as date in QlikView

Text date as date

pauldamen — Mon, 23 May 2016 08:26:11 GMT

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

Re: Text date as date

Kushal_Chawda — Mon, 23 May 2016 08:34:44 GMT

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.

Re: Text date as date

swuehl — Mon, 23 May 2016 08:34:49 GMT

Have you tried a format like

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

Re: Text date as date

anagharao — Mon, 23 May 2016 08:35:43 GMT

Hi Paul,

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

Re: Text date as date

MayilVahanan — Mon, 23 May 2016 08:37:14 GMT

Try like this

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

Re: Text date as date

pauldamen — Mon, 23 May 2016 08:43:59 GMT

Yep no luck

Re: Text date as date

swuehl — Mon, 23 May 2016 08:46:08 GMT

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?

Re: Text date as date

Mon, 23 May 2016 08:47:53 GMT

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.

Re: Text date as date

swuehl — Mon, 23 May 2016 08:49:08 GMT

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?

Re: Text date as date

MayilVahanan — Mon, 23 May 2016 08:54:15 GMT

Hi Stefan,

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

Re: Text date as date

Kushal_Chawda — Mon, 23 May 2016 08:54:33 GMT

I agreed , this would have worked Date#( YourField, 'DD-MMM-YY') as NewDateField