Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ateafilar
Not applicable

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
bwisenosimenkg
Not applicable

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

6 Replies
ashfaq_haseeb
Not applicable

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

bwisenosimenkg
Not applicable

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

Not applicable

Re: Date problem/bug

Hi try to use

date# function for the same syntax

like

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

its_anandrjs
Not applicable

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
Not applicable

Re: Date problem/bug

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

colin_albert
Not applicable

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