topic Re: Issue loading date fields from excel in QlikView

Issue loading date fields from excel

Wed, 25 Nov 2020 16:16:04 GMT

I have an excel with some date fields in format DD/MM/YYYY.

In my script I load the excel sheet loading this fields without any conversion.

Load

Init_Date,

End_Date

FROM [My Excel path]

Once data loaded I can see QlikView has loaded them as number instead of DD/MM/YYYY

I have tried to put this at the beginning of the script but without success:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

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

SET MonthNames='ene.;feb.;mar.;abr.;may.;jun.;jul.;ago.;sep.;oct.;nov.;dic.';

SET DayNames='lu.;ma.;mi.;ju.;vi.;sá.;do.';

sunny_talwar — Mon, 29 Aug 2016 10:17:17 GMT

How about doing this?

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

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

SET MonthNames='ene.;feb.;mar.;abr.;may.;jun.;jul.;ago.;sep.;oct.;nov.;dic.';

SET DayNames='lu.;ma.;mi.;ju.;vi.;sá.;do.';

Load

Date(Init_Date) as Init_Date,

Date(End_Date) as End_Date

FROM [My Excel path]

ToniKautto — Mon, 29 Aug 2016 10:36:49 GMT

Perhaps can be worth double-checking that the dates in the excel sheet are really formatted as Dates, and not General or any other data type.

Anonymous — Mon, 29 Aug 2016 10:43:44 GMT

Agreed with Toni Kautto

You might need to use Date(Date#(Field,Format)), if that is the case, like this?

Date(Date#(Init_Date, 'DD/MMM/YYYY')) as New Date