Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
chiso_chiso
Creator
Creator

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

1 Solution

Accepted Solutions
chiso_chiso
Creator
Creator
Author

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

View solution in original post

10 Replies
amit_saini
Master III
Master III

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
Creator II
Creator II

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
Creator
Creator
Author

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
Master III
Master III

Try this:

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

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

Thanks,

AS

jonathandienst
Partner
Partner

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


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
chiso_chiso
Creator
Creator
Author

Capture45.PNG

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

chiso_chiso
Creator
Creator
Author

Capture46.PNG

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

jagan
Partner
Partner

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
Creator
Creator
Author

Capture47.PNG

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