
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aligning Date formats
Gurus,
I have a date in my fact table and a date qvd which I want to use for my calendar in the model.
The date in my fact table is DateOfSale (Date format numeric i.e. 42087.327071759). The DateID field (YYYYMMDD) in the qvd is one I want to use but there is another field called DateStamp (DD/MM/YYYY hh:mm:ss).
Kindly help to align the two so that I can populate the qvd using the DateOfSale dates.
Regards, Harrison
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For some reason I have,
In Fact Load: Date(Floor(DateOfSale)) As SaleDate
In Calendar Load: Date(Date#(DateID,'YYYYMMDD')) as SaleDate
And it has worked..
Regards


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I think you can change the format of date required based on requirement. Better to keep the same data format inside your qvw. for example while loading the data from .
Thanks,
AS


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
use date() function to change the format during the store in your qvd:
=date(42087.327071759, 'YYYYMMDD')
or
=date(42087.327071759, 'DD/MM/YYYY hh:mm:ss')
Regards
MR

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So I have in my load script..
In my fact table: Date(DateOfSale,YYYYMMDD) as SaleDate
In my Calendar: DateID as SaleDate
But for some reason its not populating the Calendar Table with my SaleDate Dates.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
In my fact table: Date(DateOfSale,'YYYYMMDD')as SaleDate
In my Calendar: Date(DateID,'YYYYMMDD') as SaleDate
Thanks,
AS


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date() simply changes the display format. The dates will align on the numeric portion which is unaffected by the formatting instruction, and if they are coming from different sources, the dates may miss aligning due to minor differences.
Do both dates have a time portion? And are the time portions significant? If not, then
Date(Floor(DateOfSale)) As SaleDate
Date(Floor(DateID)) As SalesDate
If the time is meaningful, then try rounding both dates to the nearest hour (or minute or second):
Date(Round(DateOfSale, 1/24)) As SaleDate
Date(Round(DateOfSale, 1/24/60)) As SaleDate
Date(Round(DateOfSale, 1/24/60/60)) As SaleDate
Here Date() is just returning the date formatted as a date rather than as a number

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is what my three fields look like now. I now want to link DateID to the other 2.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Now I have Sales_Date in both Calendar and Fact but Calendar is not populating.

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Try like this
In fact table: Date(Date#(Floor(DateOfSale)))as SaleDate
In Calendar: Date(Date#(DateID,'YYYYMMDD')) as SaleDate
Regards,
Jagan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For some reason the DateOfSale and the SaleDate are not talking to each other. I am sure thats the missing portion.

- « Previous Replies
-
- 1
- 2
- Next Replies »