Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jenmclean
New Contributor II

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

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

(
ooxml, embedded labels, table is Sheet1);

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

Thanks!

1 Solution

Accepted Solutions

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

See attached qvw.


talk is cheap, supply exceeds demand
2 Replies

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

See attached qvw.


talk is cheap, supply exceeds demand
jenmclean
New Contributor II

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

Thanks so much!

Community Browser