topic Creating Dynamic Pay-Dates in QlikView

Creating Dynamic Pay-Dates

ofalana1674 — Wed, 08 Aug 2018 15:44:26 GMT

Hi All -

I have a table that holds original paydates for a number of employees; all these dates are in the past, with some being over two years ago. The table looks like this -

EID PayDate_Orig PayCycle

111 02/06/2015 Weekly

222 06/01/2016 Bi-Weekly

333 09/05/2017 Semi-monthly

444 01/08/2018 Monthly

I want to determine the future "NextPayDate" (after today) for each employee while also being able to determine old pay dates between the original pay date and today. I hope this makes sense.

I appreciate your help and time.

Thanks!

Re: Creating Dynamic Pay-Dates

sunny_talwar — Wed, 08 Aug 2018 16:15:50 GMT

what are the number of days used for Bi-Weekly and what are the number of days for semi-monthly?

Re: Creating Dynamic Pay-Dates

ofalana1674 — Wed, 08 Aug 2018 17:42:22 GMT

Thanks Sunny -

14 Days for Bi-Weekly

15 Days for Semi monthly

Re: Creating Dynamic Pay-Dates

sunny_talwar — Wed, 08 Aug 2018 18:55:10 GMT

Check this

Table:

LOAD *,

Date(If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num))) as PayDates

While If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num)) <= MonthStart(Today(), 2);

LOAD * INLINE [

EID, PayDate_Orig, PayCycle, Num, Type

111, 02/06/2015, Weekly, 7, Days

222, 06/01/2016, Bi-Weekly, 14, Days

333, 09/05/2017, Semi-monthly, 15, Days

444, 01/08/2018, Monthly, 1, Months

];

Re: Creating Dynamic Pay-Dates

ofalana1674 — Thu, 09 Aug 2018 15:08:28 GMT

Thanks Sunny,

What if I only want the next pay date? Just a single date for each employee, so that EID 111 shows only 8/3/2018 as the PayDate.

Re: Creating Dynamic Pay-Dates

sunny_talwar — Thu, 09 Aug 2018 15:20:58 GMT

This

Table:

LOAD *

Where PayDates > Today();

LOAD *,

Date(If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num))) as PayDates

While If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num)) <= MonthStart(Today(), 2);

LOAD * INLINE [

EID, PayDate_Orig, PayCycle, Num, Type

111, 02/06/2015, Weekly, 7, Days

222, 06/01/2016, Bi-Weekly, 14, Days

333, 09/05/2017, Semi-monthly, 15, Days

444, 01/08/2018, Monthly, 1, Months

];

Right Join (Table)

LOAD EID,

Min(PayDates) as PayDates

Resident Table

Group By EID;