Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Try this...
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
Hi ,
How to change this Dates to Normal Format !
Date(Date#(yourdate,'DD.MM.YYYY'),'DD/MM/YYYY')