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.
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
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 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:
Files.Path(text): the original path of the file
Files.ModifiedDate(date): the “last modified” date of the file
Files.ModifiedHour(number): the “last modified” hour of the file, in the UTC+00 time zone.
Files.ModifiedMinute(number): the “last modified” minute 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
Files.BadLines(number): the number of lines dropped - so
RawLines - BadLinesis 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