Date issue

[nagarjuna_kotha]

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.

[maxgro]

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

[#3]

Try this...

[MarcoWedel]

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

[nagarjuna_kotha]

Hi ,

How to change this Dates to Normal Format !

[sasiparupudi1]

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