topic Calendar - Missing values in QlikView

Calendar - Missing values

Fri, 15 Jul 2011 14:09:33 GMT

Hello fellow QlikViewers

As usual Im having nightmares with dates. Much like the Visualisation of Line Graphs discussion, Im having problems with continuous representation of data.

I have a Pivot table with two dates.

The rows are a booking date.

The columns are a travel date.

I wish to see a full list of travel dates ( ie continuous for the following 84 days ) for every booking date.

Currently, there are gaps which plays havoc with my set analysis when comparing selected year to year -1 owing to inconsistent representation of the columns in each data set.

Ive seen in some discussions that the simplest way would be to create zero values for those dates that are missing and add them to my main data table during the load script.

I assume that it would be simpler again to create an exhaustive list like this with a zero value for the dimensions I wish to use in my expressions where Booking date is between a set start and finish.

BOOKING DATE	TRAVEL DATE
15 July 2011	15 July 2011
15 July 2011	15 July 2011 +1
15 July 2011	15 July 2011 +2 thru 84
16 July 2011	etc, etc, etc

My question is, what is the code to create such an exhaustive table in QlikView. Im an Excel and Access head and can do the same to create a table to load in to QlikView, Id just prefer to do the load in QlikView itself.

Any comments greatly appreciated as always.

Moose

Calendar - Missing values

danielrozental — Fri, 15 Jul 2011 19:01:14 GMT

I think it will help understand your problem if you could post a sample app or a sample excel sheet with an example of the source data and the results you're trying to achieve.

Calendar - Missing values

Sat, 16 Jul 2011 08:31:43 GMT

Hi Moose

It sounds like you need to crerate dates for travel that don't exist in your fact table. You might want to do this for booking dates too if you need them. The script below will create them and then you need to concatenate them to your fact table either in the data creation script or in your apps. This will give you a full set of dates in the fact table, that should fix your problem. You won't have any data against these dates but they will now be available in your charts and tables.

Let vStartYear = Year(Today()) ; //Set start year to current year
Let vStart = Num(Today()) ; //Set Today as integer
Let vEnd = Today()+730 ; //Set end date to today + 2 years, (+730)days)

Concatenate (***Fact table name here***)

Load
MakeDate($(vStartYear))+RecNo() as [Booking Date],//Take the start year and create 730 dates
MakeDate($(vStartYear))+RecNo() as [Travel Date]//Take the start year and create 730 dates
Autogenerate $(vEnd) - $(vStart); // Autogenerate 730 lines

Let me know how you get on.

Best regards

Greene

Calendar - Missing values

Wed, 27 Jul 2011 14:30:51 GMT

Hi Greene

THanks for that, it works fine, but,Im trying to create a full list of travel dates for every individual booking date.

So for example:-

Booking Date 1 Jan 2010 would have Travel Dates 01 Jan 2010 to 31 Dec 2010

Booking Date 2 Jan 2010 would have Travel Dates 02 Jan 2010 to 01 Jan 2011

and so on. . . . . . . . .

Cheers

Moose