Merge/Join by nearest or lesser date

1

Example of a problem I’m trying to solve.

Assume I have a fact table with three columns (Table1).

  1. user_id
  2. user_id_creation_date
  3. favorite_team_id

A user_id is created only once.

And a lookup table (Table2)

  1. favorite_team_id
  2. winning_record (Y/N)
  3. date_of_record

So the team’s record (winning or losing) changes dynamically based on the date_of_record.

Trying to determine if the user joined when their favorite team had a winning or losing record. How to merge or lookup winning_record for favorite_team based for each user_id at the user_id_creation_date based on the nearest or lesser date_of_record ? I can do this in SQL. Just not sure how to do in EM. Any guidance?

2

Hi,

  • Join the 2 tables using the favorite_team_id column
  • make the difference between the joining date and the date of record
  • make it an absolute value
  • extract the min by user_id
  • keep the rows where the difference = the MIN value
    Then you’ll get the nearest team records regarding the user_id_date

Regards

3

It looks like you should be able to do it with the “Interval merge” action. Just calculate in two columns the boundaries of the range that you define as “the nearest or lesser date_of_record”.

4

Hi CVO and Dmitry. Thanks for the advice!