4 Replies Latest reply: Jul 19, 2017 10:32 AM by John Martin RSS

    Join and fill gaps with a Master Calendar?

    John Martin

      I've seen several examples around these forums for QlikView , however the syntax appears to have changed between QlikView and QlikSense on these Master Calendar examples. 

       

      Is anyone using / has some examples of A: Implementing a simple master Calendar , joining one or more tables, and populating null data for intermediate dates where there are gaps in those dates?

       

      For example :

       

      Say I have the below data-set -

      QLIK_data_example_01.png

      There are missing dates - so if this is joined up to a master Calendar - the data would look like :

       

      QLIK_data_example_02.png

       

      Is it possible in the load script, to work though dates and populate data into the missing dates from the last date where data existed? 

      I.E.

       

      QLIK_data_example_03.png

       

      It's doable in Excel on smaller data sets, but in this instance , I'm looking at populating around 3-4 million rows x 6 columns.

        • Re: Join and fill gaps with a Master Calendar?
          Manish Kachhia

          Temp:

          LOAD

          DATE,

          LETTER,

          NUMBER,

          TYPE,

          RANK#,

          DTNUM

          FROM

          LoadMeWorkSheet.xlsx

          (ooxml, embedded labels, table is DATA);

           

           

          Temp2:

          Load

          RowNo() as ID,

          DATE as CurrentDate,

          Previous(DATE) as NextDate,

          LETTER,

          NUMBER,

          TYPE,

          RANK#,

          DTNUM

          Resident Temp

          Order By DATE DESC;

           

           

          Drop Table Temp;

           

           

          Final:

          Load

          *,

          Date(CurrentDate + IterNo() - 1) as DATE

          Resident Temp2

          While CurrentDate + IterNo() - 1 < NextDate;

           

           

          Drop Table Temp2;

          Drop Fields CurrentDate, NextDate, ID;

            • Re: Join and fill gaps with a Master Calendar?
              John Martin

              Hi Manish - good example, if they were non-intersecting date ranges this would be perfect, but it seems where date range for one value intersects another, only one selection is entered.

               

              For example - I changed up the data to have several intersecting date ranges for each value like so ( DateCNT column is a count of matching DATE fields - in here, there are 4 entries for 1-Jun-17 and 7-Jun-17 , with 8 Unique Items in the LETTER column )

              New_range91.png

               

              However only two lines appear when filtering the selected data on that column :

               

              New_range92.png

               

              Here's the script  :

               

              [Temp]:

               

               

              LOAD

                  "DATE",

                  LETTER,

                  NUMBER,

                  "TYPE",

                  RANK#,

                  DTNUM,

                  DATECNT,

                  LETdtRANGE,

                  FIRST_DT,

                  LAST_DT

               

              FROM [lib://AttachedFiles/LoadMeWorkSheet.xlsx]

               

               

              (ooxml, embedded labels, table is DATA);

               

              Temp2:@

              Load

              RowNo()&LETTER as ID,

              DATE as CurrentDate,

              Previous(DATE) as NextDate,

                  LETTER,

                  NUMBER,

                  "TYPE",

                  RANK#,

                  DTNUM,

                  DATECNT,

                  LETdtRANGE,

                  FIRST_DT,

                  LAST_DT

              Resident Temp

              Order By DATE DESC;

               

               

              Drop Table Temp;

               

               

              Final:

              Load

              *,

              Date(CurrentDate + IterNo() - 1) as DATE

              Resident Temp2

              While CurrentDate + IterNo() - 1 < NextDate;

               

               

              Drop Table Temp2;

              //Drop Fields CurrentDate, NextDate, ID;

            • Re: Join and fill gaps with a Master Calendar?
              John Martin

              Found a version by Gysbert Wassenaar gwassenaar  -  and just modified the fields - it works and populates the intersecting ranges!


              Here's the result as a bar chart - as you can see, the fields are now stacking where multiple values exist for the date :

              nEW_RANGE_93.png


              I altered the ordering to go by LETTER  , DATE  .    Hopefully this helps someone, I must've spent a few hours scouring around, not knowing the correct terminology-  half the battle when it comes to finding the correct answer!


              TempData:

               

              LOAD

                  [DATE] as Date,

                  LETTER,

                  NUMBER,

                  [TYPE],

                  RANK#,

                  DTNUM,

                  DATECNT,

                  LETdtRANGE,

                  FIRST_DT,

                  LAST_DT

              FROM [lib://AttachedFiles/LoadMeWorkSheet.xlsx]

              (ooxml, embedded labels, table is DATA);

               

              Temp2:

              load *, if(previous(LETTER)=LETTER, previous(Date),Date+1) as NextDate

              Resident TempData

              order by LETTER, Date desc;

               

              drop table TempData;

               

              Result:

              load LETTER, date(Date + IterNo() -1) as Date,

                  NUMBER,

                  [TYPE],

                  RANK#,

                  DTNUM,

                  DATECNT,

                  LETdtRANGE,

                  FIRST_DT,

                  LAST_DT

              Resident Temp2

              while Date + IterNo() - 1 < NextDate

              order by LETTER, Date;

               

              drop table Temp2;