topic Re: Need help for Date issue in QlikView

Need help for Date issue

saradhi_it — Wed, 25 Nov 2020 16:16:04 GMT

i have two different type date format was there in excel file

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

Re: Need help for Date issue

sunny_talwar — Mon, 12 Sep 2016 17:15:21 GMT

May be like this:

Date(Alt(Num(Date), Num(Date#(Date, 'D/M/YYYY')), Num(Date#(Date, 'DD-MM-YYYY')))) as Date

Re: Need help for Date issue

MindaugasBacius — Mon, 12 Sep 2016 17:55:07 GMT

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
];

Re: Need help for Date issue

swuehl — Mon, 12 Sep 2016 18:09:06 GMT

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.

Re: Need help for Date issue

saimahasan — Tue, 13 Sep 2016 04:41:13 GMT

I agree with Stefan's reply. It works.