topic Dates aren't linking?? in QlikView

Dates aren't linking??

stevietm — Wed, 11 Mar 2015 11:58:37 GMT

Hi Ladies and Gents,

I have bit of a problem with my dates that are extracted from a sql database.

I have a first qvd generator which extracts the data and compresses it into a qvd. From this qvd i have another qvd generator which transforms the extract qvd. This is where i change the date and do all my transformations and create another qvd that will be pulled into qlikview.

All of my other models runs on the same structure and the same calendar, the date formats are the same throughout my qlikview models. "YYYY-MM-DD...."

The problem: The problem is that when i run the model there are no errors in the script and the linking is 100% - but it does not seem to link as when i select a month, quarter, year etc all of my dimensions are grayed out. But when i select my date key as a date i get a result.

Hope this makes sense.

Please see attached for more details.

Thanks

Regards

Stevie

Re: Dates aren't linking??

Wed, 11 Mar 2015 12:12:12 GMT

Hi Stevie,

your appointment date field is date and time, so the underlying numerical value is different to what is being generated in your master calendar.

Add a floor when you first create your key, so they are aligned

,Date((Floor([Appointment_Date])), 'YYYY-MM-DD') as AppointmentDateKey

Hope that helps

Joe

Re: Dates aren't linking??

stevietm — Wed, 11 Mar 2015 12:17:59 GMT

Hi Joe,

This worked fine - would you please be so kind just to explain what this "Floor" did as i never made use of this before.

Thanks for the help.

Regards

Stevie

Re: Dates aren't linking??

Wed, 11 Mar 2015 12:24:10 GMT

Floor will remove the decimal from your underlying field.

Essentially from your database field you had a value of 4899.12 for example, the number for the date and the decimal for the time element.

Within your master calendar you are auto generating whole numbers only so 4899 in this example.

So even though the two show the same date from a text point of view, the number is actually different. This explains why when you select a month, only those whole number key values remain and the key values from your fact data (with the decimal) are dropped and vice versa.

The simplest way for you to see this, is load a listbox of your key field and change the presentation to number, you'll then see the different whole and decimal value

Hope that helps

Joe

Re: Dates aren't linking??

stevietm — Wed, 11 Mar 2015 13:19:21 GMT

Hi Joe,

Thanks so much this is very helpful. This will solve a lot of future date problems.

Regards

Stevie

Re: Dates aren't linking??

Wed, 11 Mar 2015 13:22:49 GMT

no problem glad to help