topic Re: Converting a date with "." in source in QlikView

Converting a date with "." in source

sspe — Mon, 15 Dec 2014 11:38:11 GMT

Hi,

I'm reading data from an Excel file, where I have some date in format DD.MM.YYYY. I'm trying to convert these to a date in Qlikview so I can use them as dates and also dispay it a DD-MM-YYYY, but I'm struggling a little bit with it.

I have tried to convert it with DATE#(DokDate,'DD.MM.YYYY') which I'd expect to convert it to a Number that I can use in calculations and also format using the DATE function, but that doesn't seems to work.

When I convert it using DATE#(DokDate,'DD.MM.YYYY') it still displays it in the original format when I try to display it as a number, and I doesn't seems to be able to display it in the format I want.

I can make it work by doing a REPLACE(DokDate,'.','-'), but I think it should be possible to make it work with the DATE#/DATE function(s).

Maybe I'm just doing something wrong or missing the point about the DATE functions?

Regards

Steen

Re: Converting a date with "." in source

MK_QSL — Mon, 15 Dec 2014 11:40:19 GMT

Date(Date#(DokDate,'DD.MM.YYYY'),'DD-MM-YYYY') as DokDate

Re: Converting a date with "." in source

ashfaq_haseeb — Mon, 15 Dec 2014 11:41:33 GMT

Try like this

DATE(DATE#(DokDate,'DD.MM.YYYY') ,'DD-MM-YYYY')

Regards

ASHFAQ

Re: Converting a date with "." in source

MarcoWedel — Mon, 15 Dec 2014 11:56:09 GMT

Date(Date#(DokDate,'DD.MM.YYYY')) as DokDate

will convert it to the format you specified as default in your Set DateFormat = ... statement in the script.

Re: Converting a date with "." in source

Anonymous — Mon, 15 Dec 2014 15:22:57 GMT

More simple:

Create a column in excel

[Column Date] - 18991230

Column Date should be converted to YYYYMMDD.

This solutions is the fastest way to load a Date into Qlik.

Re: Converting a date with "." in source

sspe — Mon, 15 Dec 2014 15:47:08 GMT

Hi,

Thanks for you inputs. I have now got it working, but I'm not sure why. First I simply thought it was because I had use Date# and not DATE#, but when I changed it back to verify it it still worked, so apparently that was not the case.

No matter what, it works now but I might play a little bit more with to find out why it didn't worked in the first place.

Regards

Steen