topic Matching HR data to payroll data with Date issues in QlikView

Matching HR data to payroll data with Date issues

Fri, 23 Apr 2010 15:44:31 GMT

I am fairly new to QlikView and am hoping someone may be able to help me.

I have two tables of data one from ORACLE HR giving employee details and one from ORACLE PAYROLL detailing pay details and job coverage.

The way that ORACLE HR works means each amendment to a employee record is date stamped, therefore if they move departments 5 times during there employment there will be 5 individual entries.

I am trying to match data from our payroll system for an employee against his HR data identifying his department, but I am getting 5 entries i.e an entry for every amendment to his HR record.

Sounds a bit of confusing but hopefully the example below will make more sense.

Data extracted from HR

ASSIGNMENT_NUMBER

EFFECTIVE_END_DATE

EFFECTIVE_START_DATE

FIRST_NAME

GRADE

JOB_NAME

LAST_NAME

ORGANISATION_NAME

POSITION

8503238

10/10/04

15/4/02

Jon

Driver.0

Driver

Smith

Holloway

Holloway.Driver

8503238

3/6/05

11/10/04

Jon

Assistant.1

Assistant

Smith

Head Office

Head Office.Accounts Assistant

8503238

27/6/05

4/6/05

Jon

Driver.0

Driver

Smith

Holloway

Holloway.Driver

8503238

5/8/05

28/6/05

Jon

Driver.0

Driver

Smith

Holloway

Holloway.Driver

8503238

14/4/07

6/8/05

Jon

Driver.3

Driver

Smith

Holloway

Holloway.Driver

8503238

1/7/08

15/4/07

Jon

Driver.5

Driver

Smith

Holloway

Holloway.Driver

8503238

31/12/12

2/7/08

Jon

Driver.5

Driver

Smith

Holloway

Holloway.Driver

Data extracted from Payroll

ACT_PAY_VALUE

ACT_WORK_STATUS

ASSIGNMENT_NUMBER

Date

ELEMENT_NAME

PATTERN

PAYROLL_NAME

PERIOD_END_DATE

PERIOD_NAME

PERIOD_START_DATE

ROS_DUTY_CODE

ROS_PAY_VALUE

ROS_WORK_STATUS

114.41

8503238

01/02/2010

Hourly Pay

HT564

Weekly Payroll

05/02/2010

45 2010 Week

30/01/2010

HT564

114.41

When I look at the pay on the 1/2/10 for this one employee Jon Smith, the report shows it at Organisation name "Holloway" and "Head Office"

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/1803.Doc17.doc:550:0]

Look forward to your reply folks, I'm sure the answer is simple but I can't get it to work, I have tried interval match but it didnt work as i expected !

Regards

Don

Matching HR data to payroll data with Date issues

Fri, 23 Apr 2010 16:06:11 GMT

Sorry folks I'm not sure if you can see the data tables, I hve summarised below :

HR Data

Assigment Number Effective_Start_Date Effective_End_Date First_Name Last_Name Organisation_Name Grade

8503238 15/04/02 10/10/04 Jon Smith Holloway Driver.0

8503238 11/10/04 3/6/05 Jon Smith Head Office Assistant.1

8503238 4/6/05 27/06/05 Jon Smith Holloway Driver.0

8503238 28/6/05 5/8/05 Jon Smith Holloway Driver.3

8503238 6/8/05 14/4/07 Jon Smith Holloway Driver.5

8503238 15/05/07 31/12/12 Jon Smith Holloway Driver.5

PAY Data

ACT_PAY_VALUE Assigment Number Date ROS_DUTY_CODE PATTERN

114.41 8503238 1/2/2010 ht564 ht564

Hope this makes it a little clearer

Matching HR data to payroll data with Date issues

johnw — Fri, 23 Apr 2010 22:00:27 GMT

So you're trying to "join" on assignment number, and on Date being in the range of the Effective_Start_Date and Effective_End_Date for one of the records for that assignment number in the HR table?

I think this:

Load the HR table

Load the pay table

INTERVALMATCH (Date, Assignment_Number)
LOAD
Effective_Start_Date
,Effective_End_Date
,Assignment_Number
RESIDENT [HR table]
;

Matching HR data to payroll data with Date issues

Sun, 25 Apr 2010 13:14:19 GMT

Hi John,

Many thanks for your reply, I now seem to have 3 synthetic keys for the three fields : Assignment_Number, Effective_Start_Date, Effective_End_Date. I'm not sure if perhaps I have interpreted your answer correctively.

Additionally I still have the same original problem, but I am guessing the synthetic keys are causing this ?

Any hep would be greatly appeciated.

Matching HR data to payroll data with Date issues

johnw — Mon, 26 Apr 2010 18:23:24 GMT

Yeah, I left too many connections sitting out there. Sorry. The synthetic keys technically aren't causing the problem; they're just indicating to me that what I did didn't work as I expected. The "join" is still occurring on the fly to the Assignment_Number, even when the Date isn't in the range. That's not what we want.

One solution is to just join everything into one table, assuming that will work for you. I'm sure we could create a linkage table if that doesn't meet your needs, but joining is often the easiest solution. Changes in bold. See attached.

LEFT JOIN ([HR Data])
INTERVALMATCH (Date, Assignment_Number)
LOAD
Effective_Start_Date
,Effective_End_Date
,Assignment_Number
RESIDENT [HR Data]
;
LEFT JOIN ([HR Data])
LOAD *
RESIDENT [PAY Data]
;
DROP TABLE [PAY Data]
;

Matching HR data to payroll data with Date issues

Tue, 27 Apr 2010 11:19:20 GMT

Thanks a lot John, that now works perfectly. Many thanks for your assistance.