0 Replies Latest reply: Apr 6, 2017 4:38 AM by Steve McClintock RSS

    QlikView - How to

    Steve McClintock

      Hello

       

      I hope someone can help me with a query on QlikView.

       

      We are using ServiceNow to log our tasks and incidents at work.

       

      I am trying to create a dashboard showing when a call was responded and fixed and if it was within SLA.

       

      From my attachment, the first screenshot, you ca see that an Incident has and Assignment group and Assigned to.

      I have successfully created the logic to determine when the Incident metric has been allocated to the Incident Assignment group

       

      Load *,
      If([Incident Metric Definition])='Assignment Group' and [Incident Metric Definition] = 'Assigned to Duration' and [Incident Metric Value]=([Assignment group]),mi_start,null()) as Official_Start_Time
      ;

      What I need help with is to determine the first time a member (Assigned to) of that Assignment group responded to that Incident.

       

      On the second screenshot of the attachment you can see we have 2 [Incident Metric Value], Paul McGinnis and Andrew Boyd. Paul McGinnis and Andrew Boyd are both members (Assigned to) of the Assignment group OPS-Windows-Server. But Paul McGinnis responded to the call first at 2016-07-12 13:40:53, but as he is also a member of the Assignment group PS-TCS-Print my logic is not picking his start time up, but it is picking up 2016-07-14 12:51:02 for [Incident Metric Value] Andrew Boyd.  Please see my logic below.

       

      Does anyone know of a way that I can use the above logic for the Official_Start_Time to also find the first (Assigned to) of the Assignment group OPS-Windows-Server.  To give me Paul McGinnis response time of 2016-07-12 13:40:53.

       

      Any help would be appreciated.  I hope this makes sense to someone, I think I have waffled on a bit

       

      Thank you

       

      Stephen

       

      NoConcatenate
      Incident_touched_by:
      Load *,
      If(previous([Incident Metric Definition])='Assignment Group' and [Incident Metric Definition] = 'Assigned to Duration' and Previous([Incident Metric Value])=Previous([Assignment group]),mi_start,null()) as Official_Start_Time
      ;
      LOAD
      *
      Resident incident_metric
      where [Incident Metric Value] <> ''
      and Match([Incident Metric Definition],'Assignment Group','Assigned to Duration')
      order by Incident.Number asc, mi_start asc, [Incident Metric Definition] asc;
      //order by Incident.Number asc, mi_start asc, [Incident Metric Definition] desc;


      First_Touched_Map:
      Mapping Load Incident.Number,
      Official_Start_Time
      Resident Incident_touched_by
      where not IsNull(Official_Start_Time);

      Drop Table Incident_touched_by;

      min_start_temp:
      Load *,
      Interval(First_Touched_Time-[First Repsonse Contact],'dd:hh:mm:ss') as "SLA L2 Response",
      Load *,      

      ApplyMap('First_Touched_Map',Incident.Number,'No mi_start map') as First_Touched_Time
      Resident min_start;

      DROP Table min_start;
      RENAME Table min_start_temp to min_start;