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
RH
8503238
01/02/2010
Hourly Pay
HT564
Weekly Payroll
05/02/2010
45 2010 Week
30/01/2010
HT564
114.41
RH
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"
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 !
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] ;
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?
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 ?
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] ;
