Quickstart

Upload data sources

First things first, we need to provide data sources before we can query anything.

To add a data source to the Plugin, you need to go to the Connect Data pane, attach a file, enter an OPTIONS query and press the Add button.

Now you have a data source that you can reference when mapping data to shapes.

Make a query

A query must start with an instruction that selects data from an available data source. For this purpose we use SELECT keyword with a set of arguments that determine what exactly needs to be selected:

SELECT(src=1, table=1, rows=[1, 2, 3], columns=[2, 4])

To map the dataframe that we just created, we need to specify a target cell location:

SELECT(...) >> (1, 1)

Here we map it to the first cell of the dataframe that lives behind the shape we are mapping data to.

It's possible to map different data to different cells for the same shape:

SELECT(1, 1, [1], [2]) >> (2, 2)
SELECT(1, 2, [2], [3]) >> (2, 3)

You might've noticed that we didn't you argument names for the queries above. All our arguments are positional - we know that src goes first, table - 2nd, etc. Keyword arguments are useful because you don't need to remember the order of arguments. Sometimes, they may also increase readbility. They are also handly when we want to take advantage of default argument values, e.g. SELECT(columns=[3, 4]). We provided values only for the columns argument, leaving the rest to use their default values. Transform data

Most of the queries aren't that simple and require some sort of processing. We have a range of functions that can help with that. Let's say, we need to sort the first column in ascending order in our dataframe. Then this is what we would do:

SELECT(1, 1, rows=[1:4], columns=[:5]) -> SORT_BY([1], axis=1, asc=True) >> (1, 1)

This notation is a bit unusual. Normally, when we need to compose multiple functions, we nest them one into another. It could look something like this:

SORT_BY(SELECT(...), by=[1], axis=1, asc=True)

But this way we might end up with a deeply nested expression that would look something like this:

TRANPOSE(
  FORMAT(
    SORT_BY(
      SELECT(1, 1, rows=[2:4], columns=[3:5]), axis=1, asc=True
    ), prefix="", suffix="%")
)

Which is, even if properly formatted, is pretty difficult to read.

That's why we use the -> sign instead to chain functions together.

Operators

There are arithmetic +, -, * (multiplication), / (division), logical &, | and comparison <, <=, >, >=, == operators available.

The arithmetic operators work in the same way as they work on matrices (see basic operations on matrices).

Comparison operators return dataframes that contain only boolean values. Here is an example where we compare a dataset with a number and get a dataset of boolean values:

[[10, 20], [30, 40]] > 25 = [[False, False], [True, True]]

Logical operators allow us to use more complex conditional logic. If, for example, we need two conditions to be met at the same time, we can use logical AND &:

SELECT(...) > 25 & SELECT(...) < 50

If it's enough to have just one condition met, we can use logical OR |:

SELECT(...) == "agree" | SELECT(...) == "disagree"

This is useful with functions such as IIF and SIG_TEST.

Use nested queries

Some functions, such as IIF, take query expressions as their arguments:

IIF(
  SELECT(...) > 20,
  SELECT(...) * 100 -> ROUND(2),
  SELECT(...) -> ROUND()
)

There is no limit on the nesting depth. If we need to implement a conditional flow with multiple conditions, like:

IF condition A:
  ...
ELSE IF condition B:
  ...
ELSE:
  ...

we can do this with queries:

IIF(
  SELECT(...) > 20,
  IIF(
    SELECT(...) < 100,
    SELECT(...),
    SELECT(...)
  ),
  SELECT(...)
)

Use aliases

In some cases it's handy to assign a name to an expression. This way we can split one complex expression into multiple simpler ones. This can be done by using AS keyword:

SELECT(...) AS p_values;
SELECT(...) AS q_values;
SELECT(...) > 50 AS condition;

IIF(condition, p_values, q_values)

Such expressions must end with a semicolon ;

Use shorter versions of function and argument names

Consider the following expression:

SELECT(src=1, table=1, rows=[3:6], columns=[:]) -> SORT_BY(by=[1], axis="columns", asc=False) -> TRANSPOSE()

It's pretty verbose for such a basic query. We can make it much shorter by omitting argument names and using default values:

SELECT(1, 1, [3:6]) -> SORT_BY([1]) -> TRANSPOSE()

Or even shorter by also using short alias names for these functions:

S(1, 1, [3:6]) -> SB([1]) -> T()

However, we believe that finding the right balance for a given expression is key to making your expressions easy to read and follow.