topic Re: Date issue in QlikView

Date issue

nagarjuna_kotha — Tue, 01 Sep 2015 18:22:52 GMT

Hi all,

I ahve one field DOc Date,in that date format is text , if change that to Date in excel if i filter that excel its not showing what i m looking for.bocz of that issue i m not able to show in qlikview also.

Please cheak below excel and QVW.give me one right solution.

Re: Date issue

maxgro — Tue, 01 Sep 2015 18:35:10 GMT

maybe I don't understand but if I load your excel with this script

SET DateFormat='DD/MM/YYYY';

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

Directory;

LOAD Plant,

Division,

[Doc. Date]

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

all [Doc. Date] values are dates

Re: Date issue

Tue, 01 Sep 2015 19:46:45 GMT

Try this...

Re: Date issue

MarcoWedel — Tue, 01 Sep 2015 21:44:25 GMT

Hi,

you should try to clean your excel file, because it contains a mix of dates and text values.

To interpret the text values as dates while loading in QlikView you could also use something like

SET DateFormat='D-MMM-YY';
SET TimestampFormat='D-MMM-YY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

table1:
LOAD Plant, 
     Division, 
     Date(Alt([Doc. Date],Date#([Doc. Date],'DD/MM/YYYY'))) as [Doc. Date] 
FROM [https://community.qlik.com/servlet/JiveServlet/download/856861-182821/Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

which should convert the dates to your default DateFormat as well.

Hope this helps

regards

Marco

Re: Date issue

nagarjuna_kotha — Wed, 02 Sep 2015 05:28:23 GMT

Hi ,

How to change this Dates to Normal Format !

Re: Date issue

sasiparupudi1 — Wed, 02 Sep 2015 05:42:22 GMT

Date(Date#(yourdate,'DD.MM.YYYY'),'DD/MM/YYYY')