3 Replies Latest reply: Apr 8, 2014 6:12 PM by Suraj Gundapaneni RSS

    Script Help?????

    Amit Saini

      Hi folks ,

       

      Please see below this script:

      Fact_new:

      NoConcatenate

      load

          *,

          dual(if(TicketAge >=0 and TicketAge <= 7, '0-7 days',

          if(TicketAge >=8 and TicketAge <= 14, '8-14 days',

          if(TicketAge >=15 and TicketAge <= 21, '15-21 days',

          if(TicketAge >=22 and TicketAge <= 28, '22-28 days',

          if(TicketAge >=29 and TicketAge <= 60, '29-60 days',

          if(TicketAge >=61 and TicketAge <= 90, '61-90 days',

          if(TicketAge >=91 and TicketAge <= 120, '91-120 days',

          if(TicketAge >121, 'Above 121 days')))))))),

          if(TicketAge >=0 and TicketAge <= 7, 10,

          if(TicketAge >=7 and TicketAge <= 14, 20,

          if(TicketAge >=15 and TicketAge <= 21, 30,

          if(TicketAge >=22 and TicketAge <= 28, 40,

          if(TicketAge >=29 and TicketAge <= 60, 50,

          if(TicketAge >=61 and TicketAge <= 90, 60,

          if(TicketAge >=91 and TicketAge <= 120, 70,

          if(TicketAge >121, 80))))))))

          ) as TicketAgeGroup;

      load

          *,

          %OpenedDate as %DateID,

          //if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,

      //    if(isnull(%ClosedDate), 1, 0) as isNew,

          if(not isnull(%OpenedDate), 1, 0) as isOpened,

      //    if(not isnull(%ClosedDate), 1, 0) as isClosed,

          floor(today()-%OpenedTime) as TicketAge,

          if(TicketPriority = 1, 1, 0) as isPrio1,

          if(TicketPriority = 2, 1, 0) as isPrio2,

          if(TicketPriority = 3, 1, 0) as isPrio3,

          if(TicketPriority = 4, 1, 0) as isPrio4,

          if(TicketPriority = 5, 1, 0) as isPrio5,

          RowNo() as Rowno,

          applymap('map_Urgency', Urgency) as Urgency2

      Resident Fact

      where not isnull(%OpenedDate);

       

      //Concatenate

      //load

      //    *,

      //    //if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,

      //    %ClosedDate as %DateID,

      ////    if(not isnull(%ClosedDate), 1, 0) as isClosed

      ////Resident Fact

      ////where not isnull(%OpenedDate);

       

       

      drop table Fact;

       

      rename table Fact_new to Fact;

       

      drop field Urgency;

       

      RENAME field Urgency2 to Urgency;

       

      My requirement:  There is one more filed as %ClosedDate which I want to call as %DateID, so that %DateID should include data of both (%ClosedDate + %OpenedDate)

       

       

      Please help me how to do this ., already in script I'm having this %OpenedDate as %DateID, but I want to call %ClosedDate also as %DateID

       

       

       

      Thanks,

      AS

        • Re: Script Help?????
          Amit Saini

          Folks please see the attachment.

           

          Thanks,

          AS

            • Re: Script Help?????
              renjith pl

               

              Fact_new:

              NoConcatenate
              load
              *,
              dual(if(TicketAge >=0 and TicketAge <= 7, '0-7 days',
              if(TicketAge >=8 and TicketAge <= 14, '8-14 days',
              if(TicketAge >=15 and TicketAge <= 21, '15-21 days',
              if(TicketAge >=22 and TicketAge <= 28, '22-28 days',
              if(TicketAge >=29 and TicketAge <= 60, '29-60 days',
              if(TicketAge >=61 and TicketAge <= 90, '61-90 days',
              if(TicketAge >=91 and TicketAge <= 120, '91-120 days',
              if(TicketAge >121, 'Above 121 days')))))))),
              if(TicketAge >=0 and TicketAge <= 7, 10,
              if(TicketAge >=7 and TicketAge <= 14, 20,
              if(TicketAge >=15 and TicketAge <= 21, 30,
              if(TicketAge >=22 and TicketAge <= 28, 40,
              if(TicketAge >=29 and TicketAge <= 60, 50,
              if(TicketAge >=61 and TicketAge <= 90, 60,
              if(TicketAge >=91 and TicketAge <= 120, 70,
              if(TicketAge >121, 80))))))))
              )
              as TicketAgeGroup;
              load
              *,
              %OpenedDate as %DateID,
              //if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,
              // if(isnull(%ClosedDate), 1, 0) as isNew,
              if(not isnull(%OpenedDate), 1, 0) as isOpened,
              // if(not isnull(%ClosedDate), 1, 0) as isClosed,
              floor(today()-%OpenedTime) as TicketAge,
              if(TicketPriority = 1, 1, 0) as isPrio1,
              if(TicketPriority = 2, 1, 0) as isPrio2,
              if(TicketPriority = 3, 1, 0) as isPrio3,
              if(TicketPriority = 4, 1, 0) as isPrio4,
              if(TicketPriority = 5, 1, 0) as isPrio5,
              RowNo() as Rowno,
              applymap('map_Urgency', Urgency) as Urgency2
              Resident Fact
              where not isnull(%OpenedDate);

              // Please help me here how to implement %ClosedDate as %DateID with concatenate load???????
              Concatenate
              load
              *,
              //if(isnull(%ClosedDate), %OpenedDate, %ClosedDate) as %DateID,
              %ClosedDate as %DateID
              //// if(not isnull(%ClosedDate), 1, 0) as isClosed
              Resident Fact
              where not isnull(%OpenedDate);


              drop table Fact;

              rename table Fact_new to Fact;

              drop field Urgency;

              RENAME field Urgency2 to Urgency;

            • Re: Script Help?????

              What do you by %ClosedDate + %OpenedDate??