topic Problem loading dates in QlikView

Problem loading dates

gshockxcc — Mon, 19 Oct 2015 22:50:39 GMT

I am having a problem loading dates. The Date format that I have in my data is 'MMM DD YY'. For example, "Dec 01 15"

I can use subfield() to get the date and year, which seems to be working fine. But I am not able to get the month to be recognized as a Month so that I can format "Dec" as "12".

I found a related post for data formatting, but I don't understand why I have to do it this way.

Shouldn't I be able to format my data better during the load script?

Re: Problem loading dates

Oleg_Troyansky — Mon, 19 Oct 2015 23:02:26 GMT

Yes, certainly.

In your load script, use the function date# to convert your string to the proper Date field:

date#(Date, 'MMM DD YY') as ProperDate,

Then, apply the desired Date format to also format the new field any way you want:

date(date#(Date, 'MMM DD YY')) as FormattedDate,

date(date#(Date, 'MMM DD YY'), 'MM/DD/YYYY') as FormattedDate,

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Problem loading dates

gshockxcc — Tue, 20 Oct 2015 00:00:07 GMT

Thanks, Oleg. I realize now that I was using the Date( ) format incorrectly. I was giving an input of the data format that I wanted, rather than the format of my data. Everything is working as it should.

Thanks.