Example workflow

import DataFrames as DF
import DuckDB as DD

import TulipaIO: create_tbl, set_tbl_col, select
import TulipaIO.FmtSQL: @where_

con = DD.DBInterface.connect(DD.DB)
src1 = "test/data/Norse/assets-data.csv"
src2 = "test/data/Norse/assets-data-alt.csv"

base_tbl = create_tbl(con, src1; name = "base")
alt_tbl = create_tbl(con, base_tbl, src2; on = [:name], cols = [:investable], variant = "alt1")

base_valhalla = select(con, base_tbl, "name LIKE 'Valhalla_%'"; show = true);
alt_valhalla = select(con, alt_tbl, "name LIKE 'Valhalla_%'"; show = true);

where_clause = @where_(lifetime in 25:50, name % "Valhalla_%")
df = set_tbl_col(
    con,
    base_tbl,
    true;
    on = :name,
    col = :investable,
    show = true,
    where_ = where_clause,
)

@show df

Instead of using @where_ you can also write the WHERE condition as a string. So for the above example, it would be:

where_clause = "(lifetime IN 25 AND 50) AND (name LIKE 'Valhalla_%')"

NOTE: This example uses a temporary table for the whole session (see the line that defines con), to save the tables to a file, point to a file like this:

con = DD.DBInterface.connect(DD.DB("test/data/norse.duckdb"))