2 Replies Latest reply: Apr 30, 2015 2:37 PM by Jennie Elliott RSS

    Combine two rows based on start and end date to create pay periods

    Jennie Elliott

      PayPeriods.png

      In the image above, I have data that shows two week pay periods from 2007 to 2015. I need to combine this into 13 pay periods instead of 26.

       

      The following script gives me pay period flags but I want to combine it further by joining it based on PAY_PeriodNumber, PAY_StartDate and PAY_EndDate.

       

      I want the end result to look like the following for each PayPeriod:

         

      PAY_CalendarYearPAY_StartDatePAY_EndDatePP_Num
      200712/31/20061/27/2007PP1
      200812/30/20071/26/2008PP1
      200912/28/20081/24/2009PP1
      201012/27/20091/23/2010PP1
      201112/26/20101/8/2011PP1

       

      My script is as follows:

       

      PayPeriod:
      LOAD PAY_PeriodID,
      PAY_CalendarYear,
      PAY_PeriodNumber,
      PAY_PayDate,
      PAY_StartDate,
      PAY_EndDate,
      If(Match(PAY_PeriodNumber,'1','2'),'PP1', Null()) as PP1,
      If(Match(PAY_PeriodNumber,'3','4'),'PP2', Null()) as PP2,
      If(Match(PAY_PeriodNumber,'5','6'),'PP3', Null()) as PP3,
      If(Match(PAY_PeriodNumber,'7','8'),'PP4', Null()) as PP4,
      If(Match(PAY_PeriodNumber,'9','10'),'PP5',Null()) as PP5,
      If(Match(PAY_PeriodNumber,'11','12'),'PP6',Null()) as PP6,
      If(Match(PAY_PeriodNumber,'13','14'),'PP7',Null()) as PP7,
      If(Match(PAY_PeriodNumber,'15','16'),'PP8',Null()) as PP8,
      If(Match(PAY_PeriodNumber,'17','18'),'PP9',Null()) as PP9,
      If(Match(PAY_PeriodNumber,'19','20'),'PP10',Null()) as PP10,
      If(Match(PAY_PeriodNumber,'21','22'),'PP11',Null()) as PP11,
      If(Match(PAY_PeriodNumber,'23','24'),'PP12',Null()) as PP12,
      If(Match(PAY_PeriodNumber,'25','26'),'PP13',Null()) as PP13
      FROM
      [C:\Users\jelliott\Documents\PayPeriods\PayPeriodData.xlsx]
      (
      ooxml, embedded labels, table is Sheet1);

       

      Your insights and expertise in this would be very much appreciated.

       

      Thanks!