Aligning Date formats

[chiso_chiso]

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

[amit_saini]

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

[mrossoit]

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

[chiso_chiso]

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.

[amit_saini]

Try this:

In my fact table: Date(DateOfSale,'YYYYMMDD')as SaleDate

In my Calendar: Date(DateID,'YYYYMMDD') as SaleDate

Thanks,

AS

[jonathandienst]

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

[chiso_chiso]

This is what my three fields look like now. I now want to link DateID to the other 2.

[chiso_chiso]

Now I have Sales_Date in both Calendar and Fact but Calendar is not populating.

[jagan]

Hi,

Try like this

In fact table: Date(Date#(Floor(DateOfSale)))as SaleDate

In Calendar: Date(Date#(DateID,'YYYYMMDD')) as SaleDate

Regards,

Jagan

[chiso_chiso]

For some reason the DateOfSale and the SaleDate are not talking to each other. I am sure thats the missing portion.