Finding min value for date across rows in R

I have the tables below:

1st Table:

2nd Table:

Desired Output:

What I want:

1. Merge table 1 and 2 by ID only for values where table1$new is not NA. (I.e. all NA values in table1 should be filled with date values from table2)
2. After merging, merge$new dates cannot occur after rejected or expired. One solution could be finding the minimum value in each row and placing that in New.

My Code:

table2 <- q1 %>% ##Create new dataset min_val from q1##
  group_by(ID) %>%
  slice(which.min(date)) %>% ##find min value from each row##
  rename(New2 = date)   ## rename the createdatetime to New2##

merged <- table1 %>% #merge merged_final.1 to min_val##
  left_join(table2, by = 'ID') %>% 
  mutate(New = coalesce(New, New2)) %>% ##This will make sure only NA value are replaced
  select(-New2) ##drop New2 column##

merged$New <- as.Date(apply(merged[, c(2, 3, 4)], 1, FUN = min))

Issue

This last line of code does not seem to be working for me. When I run this, many of the merged$new values turn to NA and the previously NA rows of merge$rejected and merge$expired are suddenly filled with random dates.

Any help would be appreciated. Also not sure why my third table isn’t showing up in html format.

>Solution :

You can use left_join then coalesce the two columns

library(tidyverse)

left_join(df1, df2) %>%
    mutate(New = pmin(Rejected, coalesce(New, date), Expired), date = NULL)

   ID   Rejected        New    Expired
1   1 2021-02-21 2021-02-21 2021-05-20
2   1 2021-02-21 2021-02-21 2021-05-20
3   2 2021-03-21 2021-03-21 2021-05-20
4   2 2021-03-21 2021-03-21 2021-05-20
5   3 2021-05-20 2021-04-30 2021-05-20
6   3 2021-05-20 2021-04-30 2021-05-20
7   4 2021-05-20 2021-05-20 2022-06-20
8   4 2021-05-20 2021-05-20 2022-06-20
9   5 2021-05-20 2021-05-20 2022-06-20
10  5 2021-05-20 2021-05-20 2022-06-20