topic Re: Date with text problem in QlikView

Date with text problem

pgalvezt — Wed, 13 Nov 2013 12:55:46 GMT

Hi, I´ve tried to fix this error but I couldn´t. The original file comes with this problem date. Excel read the field like YY/MM/DD but like final text.

Thanks.

Re: Date with text problem

pgalvezt — Wed, 13 Nov 2013 13:58:03 GMT

Hi,

I could resolve the problem changing the format in:

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

Finally I left like this:

SET DateFormat='DD/MM/YY';

Re: Date with text problem

Anonymous — Wed, 13 Nov 2013 19:06:38 GMT

Looks like the data in excel is in format DD/MM/YY. That means you have to read it this way:

LOAD

date#(Date, 'DD/MM/YY') as Date

FROM C:\Users\pgalvez.AGROSYSTEMS\Desktop\Date.xls (biff, embedded labels, table is Sheet1$);

As for the DateFormat variable, you can keep it as is or change if you prefer. The date#() function tells how to interpret the data on load in QV. The variable DateFormat says how to present the data.

Regards,

Michael

Re: Date with text problem

Thu, 30 Oct 2014 08:49:53 GMT

Try this: Since there are two types of date formats: DD/MM/YY and MM/DD/YY

LOAD Date,alt(Date#(Date,'MM/DD/YY'),Date#(Date,'DD/MM/YY')) as FormattedDate

FROM C:\Users\pgalvez.AGROSYSTEMS\Desktop\Date.xls (biff, embedded labels, table is Sheet1$);

The FormattedDate should be correctly interpreted as date type by Qlikview and should be displayed right-aligned since Qlikview stores dates internally as numeric values.

Thanks,

Shoubhik.