2 Replies Latest reply: Oct 30, 2013 8:21 AM by Kai Hilton-Jones RSS

    For each loop - creating a summary of tickets pr month

      Hi, Im very new to QlikView.

       

      Im attempting to create a summary of information, but is not sure how the best is to go about it. Im working on a ticket system, where the current problem is that I want to see how many new tickets are opend every month of the year, how many is closed, and how many is carried over to the next month (the difference between open and closed).

       

      The table Im attempting to create should contain the following information

       

      YearMonth

      Tickets_Open

      Tickets_Closed

      Tickets_Carried_Over.

       

      This should be used to create a bar diagram with caried over as bars, and lines for open and closed, where the months are at the x-axis and the number of tickets are on the Y-axis.

       

      How do I accomplish this. I would be very greatful for explanation on why you suggest what you do.

      Thank you in advance for your help and your time.

       

      Background info:

       

      I have the following tables (and a few more, though I do not think they are relevant)

       

      Tickets:


      ASSIGNED_TECH_ID,

      CLIENT_CREATOR_ID,

      CLIENT_EMAIL,

      CLIENT_ID,

      CLIENT_NAME,

      CLOSE_DATE,

      CLOSE_YEARMONTH,

      FIRST_RESPONSE_DATE,

      Fixtime_hours,

      JOB_TICKET_ID, --> KEY

      JOB_TIME,

      LAST_CLIENT_REMINDER_DATE,

      LAST_EMAIL_SEND_DATE,

      LAST_REMINDER_DATE,

      LAST_STATUS_UPDATE_TIME,

      LAST_UPDATED,

      LOCATION_ID,

      OPEN_YEARMONTH,

      PRIORITY_TYPE_ID,

      PRIORITY_TYPE_NAME,

      PROBLEM_TYPE_ID,

      PROBLEM_TYPE_LONGNAME,

      PROBLEM_TYPE_NAME,

      QUESTION_TEXT,

      REPORT_DATE,
         Floor(REPORT_DATE) as Date,

      STATUS_TYPE_ID,

      STATUS_TYPE_NAME,

      IF(STATUS_TYPE_ID = 5, LAST_STATUS_UPDATE_TIME,
         IF(STATUS_TYPE_ID=3, CLOSE_DATE, )) as Fixdate,
         Date(Floor(Fixdate),'YYYY-MM') as Fixmonth,
         1 as TicketRecordCounter,

      SUBJECT,

      TICKET_TECH,

      TICKET_TIME_AS_OF_LAST_UPDATE,

      UPDATED_BY_TECH_FLAG,

      UPDATED_FLAG,

      WORK_END_DATE,

      WORK_START_DATE

       

      Ticket_dates:


      JOB_TICKET_ID,

      Date,

      (TimeStamp(REPORT_DATE, 'YYYY-MM-DD hh:mm')) as Ticket_Report_Date,

      Ticket_Close_Date,

      (Date(Date, 'YYYY-MM')) as Ticket_Open_YearMonth,

      (Date(CLOSE_DATE, 'YYYY-MM')) as Ticket_Close_YearMonth,

      LAST_STATUS_UPDATE_TIME,

      if(CLOSE_DATE <> LAST_STATUS_UPDATE_TIME, LAST_STATUS_UPDATE_TIME, CLOSE_DATE) as Ticket_Last_Updated,

      TICKET_TIME_AS_OF_LAST_UPDATE,

      if(CLOSE_DATE <> LAST_STATUS_UPDATE_TIME,

      (Interval(LAST_STATUS_UPDATE_TIME - REPORT_DATE, 'mm')),

      (Interval(CLOSE_DATE - REPORT_DATE,'mm'))

      ) as Ticket_Time_as_of_last_update_test2,

      if(Today()-REPORT_DATE <= 90, 'less than 3 month',

      if(Today()-REPORT_DATE >= 365, 'More than 1 year', 'More than 3 month')) as Group_Age

       

      MasterCalender:


      Date(TempDate) as Date,

      Year(TempDate) as Year,

      Month(TempDate) as Month,

      Day(TempDate) as Day,

      Week(TempDate) as Week,

      Weekday(TempDate) as Weekday,

      'Q' & Ceil(Month(TempDate)/3) as Quarter,

      Date(MonthStart(TempDate), 'YYYY-MM') as YearMonth,

      Year(TempDate) & '-' & Week(TempDate) as YearWeek
        • Re: For each loop - creating a summary of tickets pr month

          Jeg vil lave C# script som laver en array-liste hver måned(eller dag om du vil), hvor den så kigger på om der kommer nogle nye tickets ind. Er der kommet en ny ind, så må være der en ny opened, er der 1 mindre,, så er der en lukket.

           

          Du kan sammenligne client_id vil jeg tro.

           

           

          Her er et eksempel på en kode som næsten gør det samme:

           

           

           

          using System;

          using System.Data;

          using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

          using Microsoft.SqlServer.Dts.Runtime.Wrapper;

          using System.Collections.Generic;

           

           

          [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

          public class ScriptMain : UserComponent

          {

           

           

              List<int> baselineCustomerList = new List<int>();

              List<int> currentCustomerList = new List<int>();

              string optionFirstRun = "yes";

              string lastDate = "2013-03-22";

              string lastOptionID = "Ukendt";

              bool monkey = false;

              int RowCount = 0;

           

           

           

           

              public override void Input0_ProcessInputRow(Input0Buffer Row)

              {

                  RowCount++;

           

           

                  if (!lastDate.Equals(Row.DateClean))

                  {

                      optionFirstRun = "no";

           

           

                      if (currentCustomerList.Count > 0)

                      {

           

           

                          OutputBuffer.AddRow();

                          OutputBuffer.Status = Row.Status;

                          OutputBuffer.AfgangHovedfase = removeAdditions(baselineCustomerList, currentCustomerList);

                          OutputBuffer.TilgangHovedfase = findAdditions(baselineCustomerList, currentCustomerList);

                          OutputBuffer.DateClean = lastDate;

                          OutputBuffer.Milestone = lastOptionID;

           

           

                          currentCustomerList.Clear();

                          lastDate = Row.DateClean;

                          lastOptionID = Row.Milestone;

                      }

           

           

                  }

           

           

                  if (optionFirstRun.Equals("yes"))

                  {

           

           

                      baselineCustomerList.Add(Row.ItemID);

                      lastDate = Row.DateClean;

                      lastOptionID = Row.Milestone;

                  }

                  else

                  {

                      // er option skiftet? Hvis ja, s�t firstRun = yes og start forfra p� opfyldning af baseline kundeliste

                      if (lastOptionID != Row.Milestone)

                      {

                          optionFirstRun = "yes";

                          baselineCustomerList.Clear();

                          baselineCustomerList.Add(Row.ItemID);

                          lastOptionID = Row.Milestone;

                          lastDate = Row.DateClean;

                          currentCustomerList.Clear();

                      }

                      else

                      {

                          currentCustomerList.Add(Row.ItemID);

                          // ComponentMetaData.FireInformation(0, null, "currentCustomre customer add ", null, 0, ref monkey);

                          lastDate = Row.DateClean;

                          lastOptionID = Row.Milestone;

                      }

           

           

           

           

                  }

                  if (RowCount==Variables.RowcountFactCRMFlowMain)

                  {

           

           

                      OutputBuffer.AddRow();

                      OutputBuffer.Status = Row.Status;

                      OutputBuffer.AfgangHovedfase = removeAdditions(baselineCustomerList, currentCustomerList);

                      OutputBuffer.TilgangHovedfase = findAdditions(baselineCustomerList, currentCustomerList);

                      OutputBuffer.DateClean = lastDate;

                      OutputBuffer.Milestone = lastOptionID;

                  }

           

           

              }

           

           

              /// <summary>

              ///   Finds a count of elemtents in newLineListe that is not present in the baseLineList

              /// </summary>

              /// <param name="baseLineList"> the baseline list</param>

              /// <param name="newLineList">the list to check if there is items not in the baselinelist </param>

              /// <returns></returns>

              private int findAdditions(List<int> baseLineList, List<int> newLineList)

              {

           

           

           

           

                  int additionCount = 0, baseLineCount = 0;

           

           

           

           

           

           

                  foreach (int item in newLineList)

                  {

                      if (!baseLineList.Contains(item))

                      {

                          additionCount++;

                          baseLineList.Add(item);

                      }

                      baseLineCount++;

                  }

                  return additionCount;

              }

           

           

              /// <summary>

              /// Counts items which is in baselist and not in new list

              /// </summary>

              /// <param name="baseLineList"></param>

              /// <param name="newLineList"></param>

              /// <returns></returns>

              private int removeAdditions(List<int> baseLineList, List<int> newLineList)

              {

           

           

                  int removeCount = 0;

           

           

                  for (int i = baseLineList.Count - 1; i >= 0; i--)

                  {

                      if (!newLineList.Contains(baseLineList[i]))

                      {

                          baseLineList.Remove(baseLineList[i]);

                          removeCount++;

                      }

           

           

                  }

           

           

           

           

                  return removeCount;

              }

          • Re: For each loop - creating a summary of tickets pr month

            How is Tickets_Carried_Over defined?