interlace: Combine any number of tables, where each has their own time...

In SCDB: Easily Access and Maintain Time-Based Versioned Data (Slowly-Changing-Dimension)

Combine any number of tables, where each has their own time axis of validity

Description

The function "interlaces" the queries and combines their validity time axes (valid_from and valid_until) onto a single time axis.

interlace_sql() is deprecated in favor of interlace()

Usage

interlace(tables, by = NULL, colnames = NULL)

interlace_sql(tables, by = NULL, colnames = NULL)

Arguments

tables	(list(tbl_dbi(1))) The historical tables to combine.
by	(character()) The variable to merge by.
colnames	(⁠named list()⁠) If the time axes of validity is not called "valid_to" and "valid_until" inside each tbl_dbi, you can specify their names by supplying the arguments as a list: e.g. c(t1.from = "\<colname\>", t2.until = "\<colname\>"). colnames must be named in same order as as given in tables (i.e. t1, t2, t3, ...).

Value

The combination of input queries with a single, interlaced valid_from / valid_until time axis.

     The combination of input queries with a single, interlaced
             valid_from / valid_until time axis

Examples

  conn <- get_connection()

  t1 <- data.frame(key = c("A", "A", "B"),
                   obs_1   = c(1, 2, 2),
                   valid_from  = as.Date(c("2021-01-01", "2021-02-01", "2021-01-01")),
                   valid_until = as.Date(c("2021-02-01", "2021-03-01", NA))) |>
    dplyr::copy_to(conn, df = _, name = "t1")

  t2 <- data.frame(key = c("A", "B"),
                   obs_2 = c("a", "b"),
                   valid_from  = as.Date(c("2021-01-01", "2021-01-01")),
                   valid_until = as.Date(c("2021-04-01", NA))) |>
    dplyr::copy_to(conn, df = _, name = "t2")

  interlace(list(t1, t2), by = "key")

  close_connection(conn)

SCDB documentation built on Oct. 4, 2024, 1:09 a.m.