4 Replies Latest reply: Jul 26, 2018 5:48 AM by colin odonnell RSS

    Master Calender creates too many rows

    colin odonnell

      Hello all,

       

      I am looking to find missing dates pertaining to certain IDs and use the last known value.

       

      The Data Set would look like this:

       

      IDDateValue £
      A01/01/20171000
      A01/02/20171500
      A01/01/20181600
      B01/05/2015900
      B01/05/2015950
      B01/06/2017500
      C01/02/2018300
      C01/03/2018600

       

       

      The code found here works fine:

      ttps://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field#start=25

       

      It uses the max/min date of the entire data set to create a type of Master Calendar and then joins the table above to this.

       

      The table can be very large so I have created a subsequent bit of code to remove redundant dates (those that fall outside of the Min/Max date for that ID).

       

      This is fine until a) data set and/or b) max/min date range reaches a certain size and then I run into limits with the resulting table size:

      s://community.qlik.com/message/803661#803661

      QS has a limitation of 2 Billion Distinct values.

       

      To keep the final table size down, is it possible to create a Min/Max variable for each ID?

      And then use these variables for a temporary Master Calendar table.

       

      My idea here is to adapt the "Master Calendar" size to the date range of the ID, so that when the data is joined, there are no redundant date rows.

      I appreciate that this might create lots and lots of temporary tables along the way.

       

      If not, any other thoughts?

       

      Thanks,

      Colin

        • Re: Master Calender creates too many rows
          colin odonnell

          Hello All,

           

          Would any one know how to get from this:

           

             

          IDMin DateMax Date
          A01/01/201701/02/2017
          B01/05/201501/06/2017
          C01/02/201801/03/2018

           

           

          to this:

           

             

          IDDate
          A01/01/2017
          AMissing Dates
          A01/02/2017
          B01/05/2015
          BMissing Dates
          B01/06/2017
          C01/02/2018
          CMissing Dates
          C01/03/2018

           

           

          his could solve the issue.

           

          Thank you,

          Colin

            • Re: Master Calender creates too many rows
              colin odonnell

              A CrossTable has been able to solve the first part, namely transform the structure of the table from 3 columns to 2 columns.

               

              MinMaxTable1:
              CrossTable (Date1, Date2, 1)
              Load * Resident MinMaxTable;
              Drop Table MinMaxTable; // this table is the first table containing columns ID, Min Date, Max Date

               

              NoConcatenate
              MinMaxTable2:
              Load
              ID,
              Date2 as Date
              Resident MinMaxTable1
              order by ID Desc, Date2 Desc;
              Drop Table MinMaxTable1;

               

              Just need to fill in the missing dates now.

              • Re: Master Calender creates too many rows
                Olivier GAUTIER

                Hi,

                 

                from the code above populating .....,

                 

                make a group by ID to have a min and max date for each ID

                and add ID in temp_table to make join with ID

                 

                regards

                  • Re: Master Calender creates too many rows
                    colin odonnell

                    Hi,

                     

                    Yes I have created the MinMaxTable:

                     

                    I am struggling with part 2:

                    and add ID in temp_table to make join with ID

                     

                    When I do this, the correct Min Max Date does not get added to the ID.

                    It simply repeats the same Min Max Date.

                    Perhaps the Variables are not updating in my code?

                     

                    The code I used is:

                     

                    TempTable:
                    Load
                    ID,
                    [Date] as Date1
                    Resident AllData;


                    MinMaxTable:
                    Load
                    [Line ID] as ID,
                    (Max(DATE([Date],'DD/MM/YYYY')))as [MaxDate],
                    (Min(DATE([Date],'DD/MM/YYYY'))) as [MinDate]
                    Resident AllData
                    Group By [Line ID];
                    Let vMinDate = Peek('MinDate',-1,'MinMaxTable') - 1;
                    Let vMaxDate = Peek('MaxDate',-1,'MinMaxTable')    ;

                     

                    join (TempTable)
                    Load Date(recno()+$(vMinDate)) as Date
                    Autogenerate vMaxDate - vMinDate;