4 Replies Latest reply: May 10, 2016 10:15 AM by Mark Graham RSS

    Create a new field name which denotes the work start

    Mark Graham

      Hi all,

       

      I'm trying to get a new date field from my existing data set which shows the original work start time (where work ends next day).

       

      Eg:

      I have an emp. who works from 9/1/2015 11:30 PM to 9/2/2015 6:00 am. Data has every 5 min. tracking

      Emp,Starting,Work Mode,Start,End
      Paul,8/31/2015,Dual,8/31/15 11:45 PM,8/31/15 11:50 PM
      Paul,8/31/2015,Dual,8/31/15 11:50 PM,8/31/15 11:55 PM
      Paul,9/1/2015,Dual,9/1/15 12:00 AM,9/1/15 12:05 AM
      Paul,9/1/2015,Dual,9/1/15 12:05 AM,9/1/15 12:10 AM
      Paul,9/1/2015,Dual,9/1/15 12:10 AM,9/1/15 12:15 AM
      Paul,9/1/2015,Dual,9/1/15 12:15 AM,9/1/15 12:20 AM

       

      At the end of the work at 12:20 am, the start column shows as 9/1/15.

       

       

       

      I'm trying to create new filed which shows like below:

      Emp,Starting,Work Mode,Start,EndNew Field

       

      Paul,8/31/2015,Dual,8/31/15 11:45 PM,8/31/15 11:50 PM8/31/15
      Paul,8/31/2015,Dual,8/31/15 11:50 PM,8/31/15 11:55 PM8/31/15
      Paul,9/1/2015,Dual,9/1/15 12:00 AM,9/1/15 12:05 AM8/31/15
      Paul,9/1/2015,Dual,9/1/15 12:05 AM,9/1/15 12:10 AM8/31/15
      Paul,9/1/2015,Dual,9/1/15 12:10 AM,9/1/15 12:15 AM8/31/15
      Paul,9/1/2015,Dual,9/1/15 12:15 AM,9/1/15 12:20 AM8/31/15

       

      Please find attachment.

      Any help is highly appreciated.

        • Re: Create a new field name which denotes the work start
          Sunny Talwar

          May be like this Mark?

           

          Table:

          LOAD * Inline

          [

          Emp, Starting, Work Mode, Start, End,

          Paul, 8/31/2015, Dual, 8/31/15 11:45 PM, 8/31/15 11:50 PM

          Paul, 8/31/2015, Dual, 8/31/15 11:50 PM, 8/31/15 11:55 PM

          Paul, 9/1/2015, Dual, 9/1/15 12:00 AM, 9/1/15 12:05 AM

          Paul, 9/1/2015, Dual, 9/1/15 12:05 AM, 9/1/15 12:10 AM

          Paul, 9/1/2015, Dual, 9/1/15 12:10 AM, 9/1/15 12:15 AM

          Paul, 9/1/2015, Dual, 9/1/15 12:15 AM, 9/1/15 12:20 AM

          Paul, 9/1/2015, Dual, 9/1/15 12:20 AM, 9/1/15 12:25 AM

          Paul, 9/1/2015, Dual, 9/1/15 12:25 AM, 9/1/15 12:30 AM

          Paul, 9/1/2015, Dual, 9/1/15 11:45 PM, 9/1/15 11:50 PM

          Paul, 9/1/2015, Dual, 9/1/15 11:50 PM, 9/1/15 11:55 PM

          Paul, 9/2/2015, Dual, 9/2/15 12:00 AM, 9/2/15 12:05 AM

          Paul, 9/2/2015, Dual, 9/2/15 12:05 AM, 9/2/15 12:10 AM

          Paul, 9/2/2015, Dual, 9/2/15 12:10 AM, 9/2/15 12:15 AM

          ];

           

          Left Join (Table)

          LOAD Emp,

            Date(Min(Starting)) as NewField

          Resident Table

          Group By Emp;