3 Replies Latest reply: Jul 27, 2011 10:30 AM by Jason Curd RSS

    Calendar - Missing values

      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 DATETRAVEL DATE
      15 July 201115 July 2011
      15 July 201115 July 2011 +1
      15 July 201115 July 2011 +2 thru 84
      16 July 2011etc, 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

        • Calendar - Missing values
          Daniel Rozental

          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

            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

                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