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

[jenmclean]

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_CalendarYear	PAY_StartDate	PAY_EndDate	PP_Num
2007	12/31/2006	1/27/2007	PP1
2008	12/30/2007	1/26/2008	PP1
2009	12/28/2008	1/24/2009	PP1
2010	12/27/2009	1/23/2010	PP1
2011	12/26/2010	1/8/2011	PP1

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

(ooxml, embedded labels, table is Sheet1);

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

Thanks!

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[jenmclean]

Thanks so much!