Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
KR
Moose
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.
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
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