Status as of a particular date (PeopleSoft Effective Date)

[#1]

My application is an HR dashboard using v9.00, showcasing data from PeopleSoft, which is built using "effective dates". This is the part that is giving me fits. I need to show historical data. How many employees were active on any given date in time. I will have a calendar, where the user can select a date and get the headcount based on their date selection. It SHOULD be fairly simple, however I can't figure out how to do it with the effective dating criteria within PS. The data looks like this:

Employee Effective Date Action HR Status

A 2/28/2008 New Hire Active

A 1/1/2009 Position Change Active

A 3/15/2009 Raise Active

A 6/20/2009 Lay-Off Inactive

A 12/1/2009 Recall Active

So, if the user selects the date of 8/1/2009, the employee should show as inactive, because the last effective date prior to the selected date had an HR Status of Inactive. If the user selected any date between 2/28/08 and 6/19/2009, the employee would count as an active employee.

I'm not an advanced user, so I could use some help with this! This is basically the "key" to being able to move any further on my application. I'm stuck without it!

Thanks to everyone!

Jennifer

[#2]

Anybody have anything on this? I'm at a loss for what to do!

Thanks,

Jennifer

[johnw]

Probably the most typical way of handling it would be to convert your effective dates to effective date ranges, create a calendar of all dates in those ranges, and then use intervalmatch to associate the dates from the calendar with the correct records. But doing this, particularly doing it efficiently, isn't really all that simple. I started working on a sample file for you last week, but was unable to quickly get it up and running, so I don't have an example for you.

Another approach when you have just two possible values like active and inactive is to assign them numeric values of 1 and -1, then use set analysis to sum them through the selected date. Assuming everyone started as active, if the total is 1, the employee is active, else 0 and they're inactive. Similarly, to get a total headcount for a date, you just sum ALL of the records up through that date. See attached. It doesn't use your data, but it should give you the idea. In your case, since you can have multiple active records in a row, you'd probably need to create a separate table that ONLY included the record when they switched from active to inactive or vice versa. You can probably use peek() to see if you have the same or a different value than the previous record. Or perhaps previous() would be better in this case. Peek() refers to the table you are building. Previous() refers to the table you are reading in.

Neither of these are really complete solutions, or even likely the best possible solution for you. But probably some reply is better than no reply.

[#4]

John,

Thanks so much for your reply. I saw that you said you started working on a sample file for the first suggestion, but was unable to quickly get it up and running. Do you think you could put that together to show me how to do it? If I know that someone is out there helping with this solution, then I promise to be patient. I just was afraid that my question was timed poorly with the weekend. I have many other issues that are coming to play with this file, but if I could get through this one, I think I'd be in a decent place to move forward. I completely understand if you don't have time to build something, as it seems that my question is fairly difficult.

Thanks again for your assistance.

Jennifer

[johnw]

OK, I'll keep poking at what I'd started. Hopefully I can get an example of the intervalmatch approach up and running with your data.

[#6]

Whichever method you think best is appreciated. I really am in over my head here, but time is limited and so I have to make this work somehow. If the Peek/Previous method is suggested, could you assist me with the script for that? Thank you so much for your help.

[johnw]

Well, "best" is subjective and probably depends a lot on your actual application. But I suspect that the intervalmatch approach would be the most common solution to a problem like this. I managed to finish and debug what I was working on. My main problem was using SET instead of LET. Amazing how much difference one character can make when programming. Anyway, here's what I think is a working example using your data. Well, almost your data. I named employee A "Abigail", and added a second employee, "Bob".

The major down side to the intervalmatch approach is that it creates a LOT of records. I suspect that memory usage for these records is fairly minimal due to compression, since most of the data is repeated many times across them. But it's still a lot of data. Also, the technique may work for dates, but it cannot be extended to timestamps.

Still, if this works for you, it's probably what I'd use.

[#8]

Thanks for the quick response. I will play with what you've given me with my data, but I believe it will work. I'm waiting on our PeopleSoft developers to build me a custom view to help eliminate some of my other issues and I think the combination of your assistance and theirs will put me on my way. I will follow up soon and let you know how this worked for me.

Thanks!

[#9]

It looks great! I'm running into a problem with virtual memory on this step:

// Join ALL employees to this calendar, giving us every possible day for every possible employee.

LEFT JOIN ([Employee Dates])

LOAD fieldvalue('EMPLID', iterno()) AS EMPLID

AUTOGENERATE 1

WHILE len(fieldvalue('EMPLID', iterno()));

We have 43k active employees, and probably around 70k total employees in our system with effective dates back to 1950. Any ideas how to streamline this?

[johnw]

Hmmm, the automatic email I got said you only needed effective dates after 2005 or so. Has that been revised to go back to 1950?

Anyway, let me give you what I have so far. It doesn't address THAT portion of the script, but it might use less memory. Rather than duplicating all the data from every row for every date, I assign an integer ID to each row, and only link the dates to the integer IDs. It shouldn't make a big difference if QlikView's compression is good, but it might help. It just won't help for the specific part of the script where you had the trouble.