Date problem/bug

[Anonymous]

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

[ashfaq_haseeb]

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

[simenkg · Accepted solution]

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

[#4]

Hi try to use

date# function for the same syntax

like

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

[its_anandrjs]

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

[Anonymous]

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

[Colin-Albert]

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