Hello - i am struggling with writing a logic in the column "Key" of the "Fact" table below. A little background, the data in the "Dim" table pertains to employees and the "Title Eff Date" represents the date the employees' job responsibility changed. For example, employee with file number 1111 in the first table below was coded to job 40 on 01/01/2015. On 03/01/2015, his job duties were changed to job code 43 and were changed again to job code 45 on 10/02/2015.
The second table represents the punch in punch out data. For example, the first row in the Fact table states that employee 1111 punched into Department 60 on 05/30/2015.
I am trying to write a formula in the Key field of the Fact table that uses the Dim table as a lookup and assigns an appropriate key so both tables could be joined. The logic should be as follows. If employee 1111 in Fact table worked on 5/30/2015, the logic should look up all the records for the particular employee in the Dim table where the "Title Eff Date" is less than the Date on the Fact table, and then pick the key from the most recent record. For example, employee 1111 clocked on 05/30/2015 as shown in the Fact table. The second record in the Dim table indicates that he was coded to Dept 60, job 43 on 03/01/2015. If he clocked in on 05/30/2015 and the most recent change occurred on 03/01/2015, the the clock in must represent him working at dept 60, job code 43.
I have put in the right answers in in the Fact table in the first column. Any help is appreciated.
<tbody>
</tbody>
<tbody>
</tbody>
The second table represents the punch in punch out data. For example, the first row in the Fact table states that employee 1111 punched into Department 60 on 05/30/2015.
I am trying to write a formula in the Key field of the Fact table that uses the Dim table as a lookup and assigns an appropriate key so both tables could be joined. The logic should be as follows. If employee 1111 in Fact table worked on 5/30/2015, the logic should look up all the records for the particular employee in the Dim table where the "Title Eff Date" is less than the Date on the Fact table, and then pick the key from the most recent record. For example, employee 1111 clocked on 05/30/2015 as shown in the Fact table. The second record in the Dim table indicates that he was coded to Dept 60, job 43 on 03/01/2015. If he clocked in on 05/30/2015 and the most recent change occurred on 03/01/2015, the the clock in must represent him working at dept 60, job code 43.
I have put in the right answers in in the Fact table in the first column. Any help is appreciated.
| Dim | ||||
| Key | File | Dept | Job Code | Title Eff Date |
| 1 | 1111 | 60 | 40 | 1/1/2015 |
| 2 | 1111 | 60 | 43 | 3/1/2015 |
| 3 | 1111 | 60 | 45 | 10/2/2015 |
| 4 | 2222 | 60 | 54 | 2/1/2015 |
| 5 | 2222 | 60 | 56 | 10/1/2015 |
<tbody>
</tbody>
| Fact | |||
| Key | File | Dept | Date |
| 2 | 1111 | 60 | 5/30/2015 |
| 4 | 2222 | 60 | 6/6/2015 |
<tbody>
</tbody>
