Joining two tables based on date-time attributs

data1mathsdata1maths Member Posts: 27 Contributor I
edited November 2018 in Help

Hello eveyone, 

Does anybody knows how to join two tables based on a date-time atributs from each, but those attribut are not equal.

Here is the frst date-time attribut from the 1st dataset:

I need to join 2 excel files

the 1st one contine this column:

01/01/2016 00:00
01/01/2016 00:10
01/01/2016 00:20
01/01/2016 00:30
01/01/2016 00:40
01/01/2016 00:50
01/01/2016 01:00
01/01/2016 01:10
01/01/2016 01:20
01/01/2016 01:30
01/01/2016 01:40
01/01/2016 01:50
01/01/2016 02:00
01/01/2016 02:10
01/01/2016 02:20
01/01/2016 02:30
01/01/2016 02:40
01/01/2016 02:50
01/01/2016 03:00
01/01/2016 03:10
01/01/2016 03:20
01/01/2016 03:30
01/01/2016 03:40
01/01/2016 03:50
01/01/2016 04:00
01/01/2016 04:10
01/01/2016 04:20
01/01/2016 04:30
01/01/2016 04:40
01/01/2016 04:50
01/01/2016 05:00
01/01/2016 05:10
01/01/2016 05:20
01/01/2016 05:30
01/01/2016 05:40
01/01/2016 05:50
01/01/2016 06:00
01/01/2016 06:10
01/01/2016 06:20
01/01/2016 06:30
01/01/2016 06:40
01/01/2016 06:50
01/01/2016 07:00
01/01/2016 07:10
01/01/2016 07:20
01/01/2016 07:30
01/01/2016 07:40
01/01/2016 07:50
01/01/2016 08:00
01/01/2016 08:10
01/01/2016 08:20
01/01/2016 08:30
01/01/2016 08:40
01/01/2016 08:50
01/01/2016 09:00
01/01/2016 09:10
01/01/2016 09:20
01/01/2016 09:30
01/01/2016 09:40
01/01/2016 09:50
01/01/2016 10:00
01/01/2016 10:10
01/01/2016 10:20
01/01/2016 10:30
01/01/2016 10:40
01/01/2016 10:50
01/01/2016 11:00
01/01/2016 11:10
01/01/2016 11:20
01/01/2016 11:30
01/01/2016 11:40
01/01/2016 11:50
01/01/2016 12:00
01/01/2016 12:10
01/01/2016 12:20
01/01/2016 12:30
01/01/2016 12:40
01/01/2016 12:50
01/01/2016 13:00
01/01/2016 13:10
01/01/2016 13:20
01/01/2016 13:30
01/01/2016 13:40
01/01/2016 13:50
01/01/2016 14:00
01/01/2016 14:10
01/01/2016 14:20
01/01/2016 14:30
01/01/2016 14:40
01/01/2016 14:50
01/01/2016 15:00
01/01/2016 15:10
01/01/2016 15:20
01/01/2016 15:30
01/01/2016 15:40
01/01/2016 15:50
01/01/2016 16:00
01/01/2016 16:10
01/01/2016 16:20
01/01/2016 16:30
01/01/2016 16:40
01/01/2016 16:50
01/01/2016 17:00
01/01/2016 17:10
01/01/2016 17:20
01/01/2016 17:30
01/01/2016 17:40
01/01/2016 17:50
01/01/2016 18:00
01/01/2016 18:10
01/01/2016 18:20
01/01/2016 18:30
01/01/2016 18:40
01/01/2016 18:50
01/01/2016 19:00
01/01/2016 19:10
01/01/2016 19:20
01/01/2016 19:30
01/01/2016 19:40
01/01/2016 19:50
01/01/2016 20:00
01/01/2016 20:10
01/01/2016 20:20
01/01/2016 20:30
01/01/2016 20:40
01/01/2016 20:50
01/01/2016 21:00
01/01/2016 21:10
01/01/2016 21:20
01/01/2016 21:30
01/01/2016 21:40
01/01/2016 21:50
01/01/2016 22:00
01/01/2016 22:10
01/01/2016 22:20
01/01/2016 22:30
01/01/2016 22:40
01/01/2016 22:50
01/01/2016 23:00
01/01/2016 23:10
01/01/2016 23:20
01/01/2016 23:30
01/01/2016 23:40
01/01/2016 23:50

 

and the second one is like this:

01/01/2016 00:00
01/01/2016 00:10
01/01/2016 00:20
01/01/2016 00:30
01/01/2016 00:40
01/01/2016 00:50
01/01/2016 01:00
01/01/2016 01:10
01/01/2016 01:20
01/01/2016 01:30
01/01/2016 01:40
01/01/2016 01:50
01/01/2016 02:00
01/01/2016 02:10
01/01/2016 02:20
01/01/2016 02:30
01/01/2016 02:40
01/01/2016 02:50
01/01/2016 03:00
01/01/2016 03:10
01/01/2016 03:20
01/01/2016 03:30
01/01/2016 03:40
01/01/2016 03:50
01/01/2016 04:00
01/01/2016 04:10
01/01/2016 04:20
01/01/2016 04:30
01/01/2016 04:40
01/01/2016 04:50
01/01/2016 05:00
01/01/2016 05:10
01/01/2016 05:20
01/01/2016 05:30
01/01/2016 05:40
01/01/2016 05:50
01/01/2016 06:00
01/01/2016 06:10
01/01/2016 06:20
01/01/2016 06:30
01/01/2016 06:40
01/01/2016 06:50
01/01/2016 07:00
01/01/2016 07:10
01/01/2016 07:20
01/01/2016 07:30
01/01/2016 07:40
01/01/2016 07:50
01/01/2016 08:00 and so one....

 and i need to do the join based on the nearest value between these date attributs.

I really need your help.

Thank you so much.

Best Answer

  • Telcontar120Telcontar120 Posts: 1,226   Unicorn
    Solution Accepted

    Another option would be to extract the minute number (using Date to Numerical) and then round to whatever intervals you want using mod arithmetic and rounding inside Generate Attributes.  Then you should be able to create a new field that is the date plus the rounded time interval and use that to Join in RapidMiner.

    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts

Answers

  • lionelderkrikorlionelderkrikor Moderator, RapidMiner Certified Analyst, Member Posts: 747   Unicorn

    Hi @data1maths,

     

    I have difficulties to understand : The 2 date attributes you presented are equal.......

    In this case, you have just to use the Join operator....

     

    If it is not the case, can you describe exactly your dataset(s) ans what you want to obtain ?

     

    Regards,

     

    Lionel

  • SGolbertSGolbert RapidMiner Certified Analyst, Member Posts: 336   Unicorn

    Hi,

     

    From the documentation, I take that the Join operator only supports equi-joins (which represent 99% of the cases). If you have the tables in a database, your best option is to perform the joins there (SQL statement).

     

    If you are familiar with R, the library data.table support non-equi joins, so you have the option of using the Execute R operator as well.

     

    I hope it helps.

     

    Regards,

    Sebastian

    data1maths
Sign In or Register to comment.