Joining Calendar Table

[tiez]

Hello!

I've got 2 tables. One table is a Calendar table with %Y%m format. The other table contains 3 stores, if the stores have gotten a "refit" it will tell in the r_closed/r_opened columns. The months in these column are "closed time".

If the r_openend/r_closed columns contain "NA", it means that the store had no "closed" time.

I am trying to get a view of the full calendar for EACH store. Next to this calendar column (date_code) I would like to display the months where the stores are closed. If the stores didn't close it needs to show NA for the StoreID in the whole calendar table.

The best I could do is using a fuzzy_join to display the closed months in the Join, but it doesnt work for each single store ID. When I tried to put it in a pivot_wider view, the output also wasn't expected what I would want it to be.

Table Calendar:

#Creating a Calendar table with yyyy%mm% format.


date_code <- c(seq(ym('2018-01'),ym('2020-12'), by = 'month') %>% format('%Y%m'))

calendar <- data.frame(date_code)

Table Refit:

refit<-tibble::tribble(
  ~StoreID, ~RefitID,   ~r_opened,    ~r_closed,
        1L,       1L,  "201909",  "201908",
        2L,       NA,          NA,           NA,
        3L,       3L, "202010", "202009"
  )

My chunk:

calendar %>% 
        fuzzy_left_join(refit, by = c("date_code" = "r_opened", "date_code" = "r_closed"),
                  match_fun = list(`>=`, `<=`)) 

The output of the chunk:

So it does the job for putting the "closed" months in the table. Now I want this to do it for each StoreID. So a view on the full calendar for each store ID.

I am new to this forum, and would be happy if someone could help me!