Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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);
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
Hi try to use
date# function for the same syntax
like
date(date#(filed,'data format'), 'Required format)
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
Thanks a lot for the quick response. The Floor() function solves the my problem
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