List of virtual tables

The “virtual” tables are generated by the Envision runtime itself, instead of reflecting tabular input files. Here, we list the virtual tables supported by Envision.

Calendar tables

In the order to facilitate time-series analysis, Envision provides several calendar tables:

These tables facilitate calendar-based aggregations. Below, an illustrating script for a weekly aggregation:

read "/sample/Lokad_Items.tsv"
read "/sample/Lokad_Orders.tsv" as Orders

Week.sold := sum(Orders.NetAmount)
show linechart "Weekly sales" a1f4 unit: "$" with Week.sold

These tables exist in two versions, the scalar version of type [Date, *] (as illustrated in the script above), and the vector version of type [Id, Date, *].

These tables are all populated with the same date range that matches the full range of the input data, as defined by the read statements that specify a Date column. If a filter when is applied, these tables are filtered accordingly.

Files table

The Files table contains the list of all the files that have been captured by the read statements in the Envision script. This table is intended to support the design data integrity checks where, for example, files are tested against conditions related to their expected sizes.

show table "My Files" with
  Files.Path
  Files.ModifiedDate
  Files.ModifiedHour
  Files.ModifiedMinute
  Files.Alias
  Files.Bytes
  Files.Success
  Files.RawLines
  Files.BadLines
  Files.BadDates
  Files.BadNumbers
  Files.MissingValues

The fields are defined as follow: