Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI
i have two different type date format was there in excel file
like
1/7/2016 and 31-07-2016
how can convert them in qlikview as single date format.. i used date, date# and ALT function .... i want convert this date in a single date format and should be done in backend only
Regards
Padhu
May be like this:
Date(Alt(Num(Date), Num(Date#(Date, 'D/M/YYYY')), Num(Date#(Date, 'DD-MM-YYYY')))) as Date
May be like this:
Date(Alt(Num(Date), Num(Date#(Date, 'D/M/YYYY')), Num(Date#(Date, 'DD-MM-YYYY')))) as Date
Or else you could do like this:
LOAD *
,Date(date#(Replace(Date,'/', '-'), 'DD-MM-YYYY'), 'YYYY-MM-DD') as [Modified date]
INLINE [
Date
1/7/2016
31-07-2016
];
With your specific excel sample file, it might be enough to use
SET DateFormat = 'DD-MM-YYYY';
LOAD A as DateField
FROM
[https://community.qlik.com/servlet/JiveServlet/download/1121955-245013/Book1.xlsx]
(ooxml, no labels, table is Sheet1);
That's because the first format is loaded in as number and will be interpreted as date, the second format will be interpreted using the date format.
I agree with Stefan's reply. It works.