0 Replies Latest reply: Jun 8, 2018 4:17 AM by Ravi Krishna Reddy Chinta RSS

    Issue in reloading Qlik Sense "Operations Monitor" Dashboard's data

    Ravi Krishna Reddy Chinta



      I am facing issue while reloading Operations Monitor's data. The last run was on 27-Mar-2018 and it was executed without any issues then. After couple of months, today I have tried reloading the data again but unfortunately this time the execution keeps on running at TimeDIM-1 temp table level and due to which CPU utilization is reaching 100%.


      I am attaching a screenshot of it and need your help.


      Operations Monitor - Copy.jpg


      I guess the issue is at intervalmatch() in the code. Let me also share the piece of code where the issue is (a guess):


      // Build a table of every minute between my start and end date

            drop table Range;


            do while startdate < enddate



                       timestamp($(startdate) + (1/(24*60))*(recno()-1),'YYYY-MM-DD h:mm') as DateTime

                  autogenerate (24*60);


                  let startdate = num($(startdate) + 1,'###0.#####','.') ;



        // To create hour sort order which sorts backward from now(reload) -- for 24-Hour summary charts

        Let hour_now = maxLogTimeStamp_Hour;


        mapping Load

           recno()-1 & ':00' as Hour,

           if($(hour_now)-(recno()-1)>=0, $(hour_now)-(recno()-1),23+($(hour_now)-(recno()-1))+1) as hour_sort

        autogenerate (24);


        //Build time table



                DISTINCT DateTime,

                 Year(DateTime) as Year,

                 MonthName(DateTime) as Month, // replace with date(DateTime, 'MMM YYYY') for better performance

                 //WeekName(DateTime) as Week,

                 WeekStart(DateTime) as [Week Beginning],

                 date(dayname(DateTime),'MMM-DD') as Day,

                 WeekDay(DateTime) as Weekday,

                 makedate(year(DateTime),month(DateTime),day(DateTime)) as Date, 

                 Hour(DateTime)&':00' as Hour,

                 ApplyMap('hour_temp',Hour(DateTime)&':00' ) as hour_sort,

                 Minute(DateTime) as [Minute of Hour],

                timestamp(floor(DateTime,1/(24)),'MMM-DD hh:00') as [Hour Timeline],

                timestamp(floor(DateTime,10/(24*60)),'MMM-DD hh:mm') as [Ten-Minute Timeline],

                timestamp(floor(DateTime,5/(24*60)),'MMM-DD hh:mm') as [Five-Minute Timeline],

                timestamp(floor(DateTime,1/(24*60)),'MMM-DD hh:mm') as [One-Minute Timeline]


            resident tempDateTimeList;

            Drop table tempDateTimeList; 


        //Interval Match dates

            inner join Intervalmatch (DateTime) Load LogEntryPeriodStart-(1/(24*60)), LogTimeStamp Resident LogContent;





           hour_sort, DateTime,Year, Month,[Week Beginning], Day, Weekday, Date, Hour,[Minute of Hour],[Hour Timeline],[Ten-Minute Timeline],[Five-Minute Timeline],[One-Minute Timeline],

           (round(num([LogEntryPeriodStart-(1/(24*60))]+1/(24*60)),0.0006)&'|'&round(num(LogTimeStamp),0.0006)) as _TimeDIM_Link // LINK w/ LogContent

          RESIDENT TimeDIM

          order by DateTime DESC;


        drop table TimeDIM;