Getting started with text
txt.Split |
Splits text: "A,B" => "A" and "B" (in separate cells) |
txt.Join |
Joins text: "A", "B" and "C" => "A,B,C" |
txt.Reverse |
Reverses a text input: "ABC" => "CBA" |
txt.Format |
Applies a pattern: txt.Format("Hi {0}", "Fred") => "Hi Fred" |
regex.IsMatch |
Tests text against a regex (returns TRUE or FALSE) |
Getting started with tables
tbl.Create |
Creates a table from data and (optionally) headers |
tbl.SelectColumns |
Selects only certain columns from a table |
tbl.Sort |
Sorts a table based on or more columns |
tbl.Filter |
Filters a table (on a row-by-row basis) based on a filter function |
tbl.CalculateColumn |
Extends the table, adding a new column (calculated row by row) |
tbl.Group |
Aggregates a table (similar to a SQL 'group by') |
tbl.GetValue |
Extract exactly one value from a table |
tbl.GetValues |
Get all values from a table (or those in a specific column) - can be used to return Schematiq data to Excel |
Getting started with databases
db.Connect |
Connect to a database |
tbl.Connect |
Connect to a table in the database |
tbl.Group (etc) |
Behaviour equivalent to in-memory tables |
tbl.Download |
Download a full table or query result into memory |
db.Query |
Execute custom SQL |
db.Insert |
Insert rows of a Schematiq table into a database table |
db.ConnectCustom |
Connect using a custom connection string |
azTbl.ConnectCustom (etc) |
Connect to Azure Table Storage |
Supported data providers
mssql, 'sql server', etc |
MS SQL Server |
access, msaccess, accdb, mdb |
MS Access |
hana |
SAP HANA |
mysql |
MySQL |
oracle |
Oracle |
postgresql, postgres |
PostgreSQL |
snowflake |
Snowflake |
sqlite |
SQLite |
vertica |
HP Vertica |
az. and azTbl. |
Azure Table Storage |
Azure Table Storage is a NoSQL service and is supported through a dedicated group of add-in functions. All other providers use SQL-based connectivity.
Advanced table functions
tbl.Pivot |
Pivot a table (similar to an Excel pivot table) |
tbl.Unpivot |
Unpivot a table (broadly, the reverse of tbl.Pivot) |
tbl.Pack |
Group a table, creating a column of sub-tables |
tbl.Unpack |
Unpack a packed table (reverse of tbl.Pack) |
tbl.Union |
Combine two tables with the same columns |
tbl.Join |
Perform a SQL-like join (several variants possible) |
tbl.Lookup |
Search for value(s) and extract a value from that row |
tbl.SelectRows |
Select certain rows (numerically) from a table |
tbl.UpdateColumns |
Update many columns using a specified function |
Trees and ranges
tree.Create |
Create a tree from a suitable Excel range |
tree.NodeCount |
Returns the number of nodes in a tree |
tree.Union |
Combine two trees |
tree.GetValues |
Returns all values from a tree |
tree.Add |
Add a new node to a tree (by name/value) |
tree.Remove |
Remove a node from a tree (by name) |
tree.Replace |
Replace the value of a named node in a tree |
rng.Create |
Create a Schematiq range from an Excel range |
rng.Sequence |
Create a range of numbers of specific length and 'step' |
rng.Subset |
Extract a subset of a range |
rng.TopToBottom |
Combine two ranges top to bottom |
rng.LeftToRight |
Combine two ranges left to right |
|
|
Data processing (CSV, JSON, XML, HTML)
tbl.ExportDelimited |
Exports a table to CSV format |
tbl.ImportDelimited |
Imports a table from CSV (or other delimited) format |
tree.ExportJson |
Exports a tree to JSON format |
tree.ImportJson |
Imports JSON into a tree |
tbl.ExportJson |
Exports a table to JSON format |
tbl.ImportJson |
Imports JSON into a table (JSONPath syntax available) |
tree.ExportXml |
Exports a tree to XML format |
tree.ImportXml |
Imports XML into a tree |
tbl.ImportXml |
Imports XML into a table (XPath syntax available) |
tbl.ImportHtml |
Imports HTML into a table (XPath syntax available) |
Web functions
web.Get |
Creates a simple GET request |
web.Post |
Creates a POST request |
web.Response |
Executes a web request |
web.CustomRequest |
Creates a configurable web request |
File and folder operations
file.ListFiles |
List all files in a folder (wildcards accepted) |
file.ListFolders |
List all subfolders in a folder (wildcards accepted) |
file.Open |
Opens a file for reading |
file.Save |
Saves a file in a format-specific manner |
file.OpenText |
Opens a file as text for reading |
file.SaveText |
Saves a file as text |
sq.Save |
Saves an object in a custom Schematiq format |
sq.Load |
Loads an object saved using sq.Save() |
User-defined functions (UDFs)
fn.FromSnippet |
Creates a UDF from a snippet, e.g. "(x, y) => x + (2 * y)" |
fn.FromTemplate |
Creates a UDF from a specified Excel range |
xl.Load |
Loads an Excel workbook into Schematiq memory |
fn.FromWorkbook |
Creates a UDF from an Excel workbook |
fn.Call |
Executes a UDF |
fn.FixInput |
Fixes the value of a single input to a UDF |
fn.SetAsync |
Makes a UDF synchronous or asynchronous |
Date and time handling
dt.Offset |
Offsets a date (e.g. by a day, business day, week, etc) |
dt.Sequence |
Generates a sequence of dates with required periodicity |
dt.SequenceBetween |
Generates a sequences between start/end dates or times |
dt.PeriodsBetween |
Returns the number of periods in a given interval (e.g. hours in a given day - useful for handling daylight saving!) |
dt.ChangeTimeZone |
Changes a date-time from one IANA time zone to another |
Use of dt.Offset
D |
Day |
B |
Business day |
W |
Week |
M |
Month |
Q |
Quarter |
S |
Commodities season (Apr-Sep, Oct-Mar) |
Y |
Year |
H |
Hour |
' |
Minute |
" |
Second |
@ |
At exactly |
+ |
Move forward |
- |
Move backwards |
< |
Move to previous |
<= |
Move to previous (unless currently at exact) |
> |
Move to next |
>= |
Move to next (unless currently at exact) |
Operators can be combined, for example -B@18H30' means "the previous business day at 18:30". The same syntax can be used for sequences, for example dt.Sequence(TODAY(), 6, "2M") will generate a year's worth of 2-month intervals.
|
