topic Re: Date Formatting Problems......... in QlikView

Date Formatting Problems.........

Fri, 27 Mar 2015 15:29:00 GMT

Hi Everyone,

I have a quite simple problem that I cannot seem to solve.

I have numerous date fields across many tables in my data model.

The format of the date is as follows: YYYY-MM-DDThh:mm:ss

I was thinking maybe the 'T' in the middle is confusing things?

Qlikview doesn't seem to want to recognise this as a date.

Should I be setting this as the Timestamp Format?

I am wanting to be able to pull out Year, Month, Week, Day and build a Master Callendar.

Any help or tips on this would be much appreciated.

Thanks in advance!

Fri, 27 Mar 2015 18:28:21 GMT

You can take only the date by using this code:

date(left('YYYY-MM-DDThh:mm:ss',10),'YYYY-MM-DD') as DateField

So the begining of your master calendar will be something like that:

Temp:

Load

min(DateField) as minDate,

max(DateField) as maxDate

Resident Fato_Final;

Let varMinDate = Num(Date#(Peek('minDate', 0, 'Temp'),'YYYY-MM-DD'));

Let varMaxDate = Num(Date#(Peek('maxDate', 0, 'Temp'),'YYYY-MM-DD'));

DROP Table Temp;

Hope that works

Sun, 29 Mar 2015 15:12:09 GMT

Cheers for the tips!

Anonymous — Sun, 29 Mar 2015 20:03:35 GMT

I'd rather use timestamp#() instead of string functions, e,g,:

date(floor(timestamp#("Your Date Field", 'YYYY-MM-DDThh:mm:ss'))) as DateField