Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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