topic Re: Aligning Date formats in QlikView

Aligning Date formats

chiso_chiso — Tue, 12 May 2015 07:38:21 GMT

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

Re: Aligning Date formats

amit_saini — Tue, 12 May 2015 07:43:39 GMT

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,

Re: Aligning Date formats

mrossoit — Tue, 12 May 2015 07:57:54 GMT

Hi,

use date() function to change the format during the store in your qvd:

=date(42087.327071759, 'YYYYMMDD')

=date(42087.327071759, 'DD/MM/YYYY hh:mm:ss')

Regards

Re: Aligning Date formats

chiso_chiso — Tue, 12 May 2015 08:35:20 GMT

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.

Re: Aligning Date formats

amit_saini — Tue, 12 May 2015 08:38:34 GMT

Try this:

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

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

Thanks,

Re: Aligning Date formats

jonathandienst — Tue, 12 May 2015 08:45:55 GMT

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

Re: Aligning Date formats

chiso_chiso — Tue, 12 May 2015 08:49:49 GMT

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

Re: Aligning Date formats

chiso_chiso — Tue, 12 May 2015 08:55:15 GMT

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

Re: Aligning Date formats

jagan — Tue, 12 May 2015 09:02:23 GMT

Hi,

Try like this

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

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

Regards,

Jagan

Re: Aligning Date formats

chiso_chiso — Tue, 12 May 2015 09:11:22 GMT

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

Re: Aligning Date formats

chiso_chiso — Tue, 12 May 2015 09:37:22 GMT

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