Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ravi_krishna_re
New Contributor III

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

Hi,

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

            tempDateTimeList:

            load

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

            autogenerate (24*60);

           

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

      loop

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

  Let hour_now = maxLogTimeStamp_Hour;

  hour_temp:

  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

      TimeDIM:

      LOAD

          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;

datetime:

    NoConcatenate

    Load

     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;

Thanks,

Ravi