Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ateafilar
Contributor

Date problem/bug

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:


current_selections.JPG.jpg

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

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Date problem/bug

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

View solution in original post

6 Replies

Re: Date problem/bug

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

Partner
Partner

Re: Date problem/bug

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

View solution in original post

Not applicable

Re: Date problem/bug

Hi try to use

date# function for the same syntax

like

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

Re: Date problem/bug

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


ateafilar
Contributor

Re: Date problem/bug

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

Re: Date problem/bug

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