topic Re: Date problem/bug in QlikView

Date problem/bug

Anonymous — Mon, 08 Dec 2014 08:55:18 GMT

Hi everyone,

I have now experienced a problem with dates that I have never seen before... I get different date values in the load of my fact table, even though I use the Date() function, and it's the same source. It's also the same date format in this source, and there are only a few transactions that gets "another" date. I can point out that fact when selecting two different values (of the same date in my current selections:

Here is my code:

Fact:

Load

RecNo() As Recno,

Date(MyDate, 'YYYY-MM-DD') As Bokföringsdatum,

... more fields ...

FROM MySource.qvd (qvd);

Calendar:

Load

Date(ISODate, 'YYYY-MM-DD') As Bokföringsdatum,

Year(ISODate) As MyYear,

Month(ISODate) As MyMonth

FROM MyCalendar.qvd (qvd);

Of course I want only one value of my date 2014-01-31... As it is now, only one of these values matches to my Calendar table, and the transactions with the "wrong" date format, doesn't get Year or Month values...

I hope you can understand my problem, and hopes of any solution to this.

Best regards,

Filip

Re: Date problem/bug

ashfaq_haseeb — Mon, 08 Dec 2014 09:01:34 GMT

Hi,

Try like below

Fact:

Load

RecNo() As Recno,

Date(floor(MyDate), 'YYYY-MM-DD') As Bokföringsdatum,

... more fields ...

FROM MySource.qvd (qvd);

Calendar:

Load

Date(floor(ISODate), 'YYYY-MM-DD') As Bokföringsdatum,

Year(ISODate) As MyYear,

Month(ISODate) As MyMonth

FROM MyCalendar.qvd (qvd);

Re: Date problem/bug

simenkg — Mon, 08 Dec 2014 09:08:35 GMT

As Ashfaq indicates the problem could be that there is also a timestamp in Bokföringsdatum.

The Date()-function does not round the date, it just formats it. It is a Dual() that will contain both the text date and the date as a number. If that number is 41670,01 in one and 41670,03 in another, they will both have 2014-01-31 as their textual representation but will be two different "dates".

The solution is to round them both down to start of the day. This is done with Date(Floor(ISODate, 'YYYY-MM-DD')) As Bokföringsdatum

Re: Date problem/bug

Mon, 08 Dec 2014 09:08:55 GMT

Hi try to use

date# function for the same syntax

date(date#(filed,'data format'), 'Required format)

Re: Date problem/bug

its_anandrjs — Mon, 08 Dec 2014 09:13:11 GMT

Hi,

Try with

Fact:

Load

RecNo() As Recno,

Date(Date#(MyDate, 'YYYY-MM-DD'), 'YYYY-MM-DD') As Bokföringsdatum,

... more fields ...

FROM MySource.qvd (qvd);

and how you create your calendar let me know.

Regards

Anand

Re: Date problem/bug

Anonymous — Mon, 08 Dec 2014 09:13:31 GMT

Thanks a lot for the quick response. The Floor() function solves the my problem

Re: Date problem/bug

Colin-Albert — Mon, 08 Dec 2014 09:15:39 GMT

This blog by Henric Cronström shows how to correctly format dates, and the difference between the Date#() and Date() functions.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/12/02/the-date-function