3 Replies Latest reply: May 2, 2018 10:51 AM by Marcus Sommer RSS

    Master Calendar slow to load in qlik sense

    Cherl Zehnder

      i'm wondering i'm creating this master calendar correctly, it seems to take quite a while on this part

      Temp:

      Load

      min("Valuation Date") as minDate,

      max("Valuation Date") as maxDate

      Resident WR_MTD_COMB_DTL_F;

       

       

      Here's the entire code, After all is said and done I really only care about month end date and i'd like to order it descending but that doesn't seem to work either.  Any help would be great.

       

      Thanks

       

      WR_MTD_COMB_DTL_F:
      LOAD *
      FROM [lib://QVD/CombinedMTDDetailFact3.qvd]
      (qvd);

      QuartersMap:
      MAPPING Load
      rowno() as Month,
      'Q' & Ceil(RowNo()/3) as Quarter
      AUTOGENERATE (12);

      Temp:
      Load
      min("Valuation Date") as minDate,
      max("Valuation Date") as maxDate
      Resident WR_MTD_COMB_DTL_F;

      // Temp:
      // Load
      //  min(FieldValue("Policy Date",recno()))-1 as mindate,
      //  max(FieldValue("Policy Date",recno())) as maxdate
      //  AUTOGENERATE FieldValueCount("Policy Date");

      Let varMinDate = Num(Peek('minDate',0,'Temp'));
      Let varMaxDate = Num(Peek('maxDate',0,'Temp'));
      DROP Table Temp;

      TempCalendar:
      Load
      $(varMinDate) + Iterno()-1 as Num,
      Date($(varMinDate)+Iterno()-1) as TempDate
      AutoGenerate 1 While $(varMinDate)+IterNo()-1 <= $(varMaxDate);

      ValuationCalendar:
      Load
      TempDate AS "Valuation Date",
      DATE(monthend(TempDate)) AS "Valuation MonthEnd Date",
      //  week(TempDate) as "Valuation Week",
      //  Year(TempDate) as "Valuation Year",
      //  Month(TempDate) as "Valuation Month",
      //  Day(TempDate) as "Valuation Day",
      ApplyMap('QuartersMap',month(TempDate),Null()) as "Valuation Quarter"
      //  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as "Valuation Week Year",
      //  WeekDay(TempDate) as "Valuation Week Day"
      Resident TempCalendar
      Order By TempDate DESC;
      Drop Table TempCalendar;

      Store ValuationCalendar into [lib://QVD/ValuationCalendar.qvd](qvd);


      Drop Tables ValuationCalendar;