virtual tables

List of virtual tables in Envision

Home » Resources » Here

This page lists the virtual tables supported by Envision. Those tables are typically generated by the Envision runtime itself, instead of directly reflecting tabular input files.

Calendar tables

In the order to facilitate time-series analysis, Envision provides several calendar tables:
  • Day: per day
  • Week: per week
  • Month: per month
  • WeekDay: per day of the week

Those tables facilitate calendar-based aggregation. 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 with Week.sold
Those 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, *].

Those tables are 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, those 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

The fields are defined as follow:
  • Files.Path (text): the original path of the file
  • Files.ModifiedDate (date): the "last modified" date of the file
  • Files.Alias (text): the namespace associated with the file
  • Files.Bytes (number): the original file size, in bytes
  • Files.Success (Boolean): whether the file was successfully loaded
  • Files.RawLines (number): the number of lines in the file, including those that were dropped (e.g. missing id or date values)
  • Files.BadLines (number): the number of lines dropped - so RawLines - BadLines is the size of the actual file processed
  • Files.BadDates (number): the number of bad date errors
  • Files.BadNumbers (number): the number of bad number errors
  • Files.MissingValues (number): the number of missing value errors