Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ofalana1674
Contributor
Contributor

Creating Dynamic Pay-Dates

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!

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

5 Replies
sunny_talwar

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

ofalana1674
Contributor
Contributor
Author

Thanks Sunny -

14 Days for Bi-Weekly

15 Days for Semi monthly

sunny_talwar

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

];


Capture.PNG

ofalana1674
Contributor
Contributor
Author

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.

sunny_talwar

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;


Capture.PNG

View solution in original post