2 Replies Latest reply: Nov 28, 2017 1:41 AM by Raghav B RSS

    Help in Script

    Raghav B

      Hi All,

       

      Requirement :

      .Today-Coursedate>Refreshperiod , 'Expired'
      .Today-Coursedate<=Refreshperiod , 'Completed'
      .Coursedate is null = 'Enrolled'


      .For any Engineer if any course status for a Program is having atleast one expired status Program status for that program will be expired
      .For any Engineer If all course status for a Program is completed then only program status for that program is completed
      .For any Engineer If Course status for a Program is having combination of only Enrolled and Completed then program status for that program is Enrolled

       

      This is my script

       

      T1:

      LOAD [ENGINEER],[PROGRAM]

      from ...

       

      T2:

      LEFT Join(T1)

      // preceeding load

      Load * , if( Indicator =1, 'Enrolled',if(Difference> REFRESHPERIOD,'Expired',
      if(Difference <= REFRESHPERIOD,'Completed',PROGSTATUS))) as CourseStatus;

       

      Load [ENGINEER],Course,CourseStatus,CourseDate,Refreshperiod,

      if(IsNull(datemodified) or datemodified = '-' or datemodified = '' or datemodified = ' ',1,0) as Indicator,

      date(Today(),'MM/DD/YYYY')-date(datemodified,'MM/DD/YYYY') as Difference

      from ..

       

      Left Join(T1)

      load distinct PROGRAM ,CourseStatus as ProgramStatusExipred
      Resident T1 where CourseStatus = 'Expired' and not IsNull(PROGRAM);

      load distinct PROGRAM as ProgramExpired
      Resident T1 where CourseStatus = 'Expired' and not IsNull(PROGRAM);

      Left Join(T1)
      load distinct PROGRAM ,CourseStatus as ProgramStatusEnrolled
      Resident T1 where not Exists(ProgramExpired,PROGRAM) and CourseStatus = 'Enrolled'  and not IsNull(PROGRAM);

      final:
      load *, if(ProgramStatusExipred='Expired','Expired',if(ProgramStatusEnrolled='Enrolled','Enrolled',CourseStatus )) as ProgramStatus Resident T1;

      drop table T1;

       

      With this logic I got table dat as below.

       

      Engineer Program ProgramStatus Course CourseStatus CourseDate Refreshperiod
      ABC P1 Expired C1.1 Expired 11/20/2017 0
      ABC P1 Expired C1.2 Completed 11/21/2017 33
      ABC P1 Expired C1.3 Completed 11/22/2017 45
      ABC P2 Expired C2.1 Expired 11/23/2017 0
      ABC P2 Expired C2.2 Completed 11/24/2017 60
      ABC P2 Expired C2.3 Completed 11/25/2017 80
      ABC P3 Enrolled C3 Completed 11/26/2017 90
      BCD P1 Expired C1.1 Completed 11/27/2017 33
      BCD P1 Expired C1.2 Completed 11/28/2017 45
      BCD P1 Expired C1.3 Expired 10/20/2017 0
      BCD P2 Expired C2.1 Completed 11/30/2017 60
      BCD P2 Expired C2.2 Completed 12/1/2017 80
      BCD P3 Enrolled C3 Completed 12/2/2017 90
      BCD P3 Enrolled C3.1 Completed 12/3/2017 45
      CDE P1 Expired C1.1 Completed 12/4/2017 1095
      CDE P1 Expired C1.2 Completed 12/5/2017 60
      CDE P1 Expired C1.3 Completed 12/6/2017 80
      CDE P2 Expired C2.1 Completed 12/7/2017 90
      CDE P2 Expired C2.2 Completed 12/8/2017 1095
      CDE P3 Enrolled C3 Completed 12/9/2017 60
      CDE P3 Enrolled C3.1 Completed 12/10/2017 80
      CDE P3 Enrolled C3.2 Completed 12/11/2017 1095
      CDE P3 Enrolled C3.3 Enrolled
      DEF P1 Expired C1.1 Completed 12/4/2017 1095
      DEF P1 Expired C1.2 Completed 12/5/2017 60
      DEF P1 Expired C1.3 Completed 12/6/2017 80
      DEF P2 Expired C2.1 Completed 12/7/2017 90
      DEF P2 Expired C2.2 Completed 12/8/2017 1095

       

       

      However I need Program status independent of Resource. Plz help

       

       

      EngineerProgramProgramStatusCourseCourseStatusCourseDateRefreshperiod
      ABCP1ExpiredC1.1Expired11/20/20170
      ABCP1ExpiredC1.2Completed11/21/201733
      ABCP1ExpiredC1.3Completed11/22/201745
      ABCP2ExpiredC2.1Expired11/23/20170
      ABCP2ExpiredC2.2Completed11/24/201760
      ABCP2ExpiredC2.3Completed11/25/201780
      ABCP3CompletedC3Completed11/26/201790
      BCDP1ExpiredC1.1Completed11/27/201733
      BCDP1ExpiredC1.2Completed11/28/201745
      BCDP1ExpiredC1.3Expired10/20/20170
      BCDP2CompletedC2.1Completed11/30/201760
      BCDP2CompletedC2.2Completed12/1/201780
      BCDP3CompletedC3Completed12/2/201790
      BCDP3CompletedC3.1Completed12/3/201745
      CDEP1CompletedC1.1Completed12/4/20171095
      CDEP1CompletedC1.2Completed12/5/201760
      CDEP1CompletedC1.3Completed12/6/201780
      CDEP2CompletedC2.1Completed12/7/201790
      CDEP2CompletedC2.2Completed12/8/20171095
      CDEP3EnrolledC3Completed12/9/201760
      CDEP3EnrolledC3.1Completed12/10/201780
      CDEP3EnrolledC3.2Completed12/11/20171095
      CDEP3EnrolledC3.3Enrolled
      DEFP1CompletedC1.1Completed12/4/20171095
      DEFP1CompletedC1.2Completed12/5/201760
      DEFP1CompletedC1.3Completed12/6/201780
      DEFP2CompletedC2.1Completed12/7/201790
      DEFP2CompletedC2.2Completed12/8/20171095

       

       

       

        • Re: Help in Script
          Settu Periyasamy

          However I need Program status independent of Resource.


          Do you mean the program status should be in another table?  If so, you can create the Keyfield in your original table, and do the resident of that table with Keyfield, and Program status field. If not, can you explain further?

            • Re: Help in Script
              Raghav B

              Hi,

               

              Program status need not to be in another table as we are deriving it from course status.

               

              what I need is program status should vary according to resource name.

               

              if you see in table/excel data 1

              irrespective of resource program status is expired.

               

              I need data as per table/excel data 2