5 Replies Latest reply: Jun 21, 2018 6:38 AM by Mahitha M RSS

    Flag creation help

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

       

      I have to create a flag with completed,Not Completed and Outstanding records. Some training completion dates contains null like below.

      Employee    HireDate            Training Completion date           Flag

          ABC           12/03/2018                  -                                       Not Completed

          DEF           14/05/2018        18/05/2018                                 Completed

          GHI            04/06/2018               -                                           Outstanding

       

      completed       --> When user completes the training  within 30days from the Hire date

      Not Completed--> When user Not completed the training  within 30days from the Hire date

      Outstanding    --> When user has still time to complete from hire date out of 30days.


      I have tried like below but getting Not completed for 04/06/2018. This employee has the deadline up to 04/07/2018 by adding 30days .Please help me to show outstanding.

      if((Training Completion date-HireDate )<=30,Completed,Not Completed)


       

      Thanks in advance.

        • Re: Flag creation help
          Jaume Criballés Faja

          Hi,

           

          This is working for me:

           

          Test:

          Load*,

          if([Training Completion date]-HireDate<=30,'Completed',

          if(Today()-HireDate<=30,'Outstanding','Not Completed')) as Status

          ;

          LOAD * INLINE [

          HireDate   ,         Training Completion date

           

           

          12/03/2018  ,                -                                      

           

           

          14/05/2018 ,       18/05/2018                         

           

           

          04/06/2018 ,              -                                     

          ];

          • Re: Flag creation help
            omar bensalem


            Try:

            if(len(trim("Training Completion date"))=0,'Outstanding',if("Training Completion date"-HireDate )<=30,'Completed','Not Completed'))


              • Re: Flag creation help
                Mahitha M

                Hi Omar,

                 

                Thanks for your reply.

                By taking len(trim("Training Completion date"))=0 for 12/03/2018 also getting 'Outstanding' its more than 30days from hire date so need to show not completed.


                04/06/2018 is not more than 30 days from hire date so need to show 'Outstanding'

              • Re: Flag creation help
                rajesh Gurram

                Make sure date fields must be date format.

                 

                If(len(Training Completion date),'Completed',

                    If((Today()-"HireDate")>30,'Not Completed',

                    If((Today()-"HireDate")<30,'Outstanding'))) as Flag