Forum Discussion

bgierwi2's avatar
bgierwi2
Advocate I
9 months ago
Solved

Joining 2 Data Sources with a Time Condition

I am looking to join 2 data sources, that come from 2 indpendent sources.

The goal is to make sure that the railcar numbers match between the two.

But we use the same railcars, so I only want to join the 2 sources if they are within 2 weeks of each other.  Ideally start with the batch number date as day 1 and match if the railcar date is within 2 weeks of that

 

This is sample data for the first data source:

Batch Car Data

Batch NumberRailcar NumberDate
A110/1/2025
210/2/2025
C310/3/2025
D410/4/2025

 

This is the second data source

Railcar Data

Railcar NumberDate
110/2/2025
210/3/2025
3310/4/2025
412/4/2025

 

 

I am hoping to combine them, and have the final result look like this:

Batch Number - BatchRailcar Number - BatchDate - BatchRailcar Number - RailDate - Rail
A110/1/2025110/2/2025
210/2/2025210/3/2025
C310/3/2025  
   3310/4/2025
D410/4/2025  
   412/4/2025

 

The top 2 rows match because the railcar numbers are the same, and the dates are within 2 weeks of each other

The 3rd entry didn't match because the railcar numbers are different (3 vs 33).  So each row matched with an empty field

And the 4th entry didnt match because the date was greater than 2 weeks.  So each row matched with an empty field.

Every entry should have a partner, we are just checking for errors.

 

I could not figure out how to match and get the time component incoprorated and for each row to match with an empty.

Or I am open to any ideas if there is a better way to check for errors

 

6 Replies

  • amitchandak 

     

    That worked!

    I would have not though to merge it twice like you did, but my data set is showing the data the way it should.

    Thank you so much!  This is really helpful, exactly what I was looking for.

    • bgierwi2's avatar
      bgierwi2
      Advocate I

      amitchandak 

       

      A full outer join would merge the tables the way I would like.

      Is there a way to incoprorate a date condition?

      I would only like to have them joined if the dates of the row are within 2 weeks of each other.  If the difference in dates is more than 2 weeks, I don't want them to match.

      • amitchandak's avatar
        amitchandak
        Super User

        bgierwi2 , Create a new column with date diff after merge and remove the rows that is greater than 2 weeks 
        if( Durations.Days([Date2] -[Date]) >14, 1,0) 

        Remove rows with value 1 of filter and keep rows with  0