List of virtual tables in Envision

List of virtual tables












Home » Resources » Here

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

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 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 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 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, 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
  Files.Path
  Files.ModifiedDate
  Files.Alias
  Files.Bytes
  Files.Success
  Files.RawLines
  Files.BadLines
  Files.BadDates
  Files.BadNumbers
  Files.MissingValues

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