Many to Many Linked Tables

[Anonymous]

Sorry for the ambiguous title, but I don't know the right words to use.

I have an issue while linking three tables where one is missing a necessary unique identifier.

The first table includes:

Person ID(unique to each employee)

Job ID(unique to each job posting)

Application Date(Date the employee applies)

The second table comes from a different system, but links easily with the same fields:

Person ID

Job ID

Hire Date(Date which the employee accepts an offer)

The issue comes with linking in the third table:

Person ID

Start Date(Day the employee starts in that job)

I need to find some way to identify the start date for each Job ID as the first Start Date for that employee that comes after the hire date of the job. So:

Any help would be appreciated.

Thank You,

Dan

[MK9885]

why not concatenate first 2 tables since fields are same except dates...

And 3 table would link to your single tables based on Person ID

Not sure if this would work...?

Fact:

Load

Person ID

Job ID

Application Date

0 as Hire Date

From App Data;

Concatenate

Load

Person ID

Job ID

0 as Application Date

Hire Date

From Hire Data;

StartTable:

Load

Person ID

Start Date

From Start Data;

If I could I'll concatenate all 3 tables but other may have different or easy approach

[Anonymous]

They are coming from three separate systems with direct pipes to Qlik and the examples were simplified for posting sake. They are all tables with cell counts in the 8-9 figure range. I could write a script in Excel to concatenate, but that defeats the purpose. The issue is that one employee might have five start dates. So I need to identify what start date belongs to which Employee/Job Id combination.

[MK9885]

Could you post a sample data in xl with all your columns?