Peliqan documentation

Joining data

Use the Peliqan Join wizard to join two tables together, using a so called “Join”. Note that you can join tables from different sources, which is not possible in regular databases. This is possible thanks to Peliqan’s federated query engine.

There are different ways to join data, each explained below:

  • Join wizard: select 2 tables (left/right)
  • Joins in SQL editor: write your own SQL query with a JOIN statement
  • Join formulas: add a column of type “formula” and use a join formula

Join wizard

In the join wizard you can select 2 tables to be joined, a left and a right table.

If you want to join 3 or more tables, you should join 2 tables first, join this resulting table with a third table, and so on.

image

Joins in SQL query editor

In the SQL query editor, you can write any type of SELECT query, including a query with a JOIN statement.

Join formulas

You can add a formula column in a table, which will show data from another table. Behind the scenes, a join will be performed. Following formulas are available:

  • Join_lookup: show a field from matching records in another table
  • Join_count: count matching records from another table
  • Join_sum: get the sum of one column of matching records from another table
  • Join_max: get the maximum of one column of matching records from another table
  • Join_min: get the minimum of one column of matching records from another table
  • Join_avg: get the average of one column of matching records from another table

Circular references

Be careful for circular references, for example using a join formula in table A that references table B, and another join formula in table B that references table A.

This type of circular references is possible but you will have to use “dot notation” in at least one of the formulas, when referencing the other table. In other words, do not use “underscore notation” in both formulas.

More info on dot notation and underscore notation:

Different types of joins

The different types of joins are described below:

  • Left join: take all rows from the left table and match with the right table; right table columns could be NULL in join.
  • Right join: take all rows from the right table and match with the left table; left table columns could be NULL in join.
  • Inner join: take only rows that match on both sides; not all rows if left table and not all rows from right table will be included.
  • Outer join: combine every row from the left table with every row from the right table; if there are 5 rows left and 3 rows right, there will be 5 x 3 = 15 rows in the result
  • Fuzz join: match rows as best as possible, use this if there is no key (e.g. an ID) to join on. For example “ACME Inc” will be joined with “ACME” if that is the best match.

Preparing data for joins

In order to join tables, you need a common key between both tables, for example a customer_id. However, when joining data from different sources, such a key is not always available. In that case you could try a fuzzy match but that is not exact.

In order to prepare data for a join, you might need to transform the data first. For example the common key could be a VAT number, but in one table the VAT number contains a country code or dots, and in the other table the VAT number does not.

In this case you can apply a transformation on the VAT number in the left table first, and then use the transformed column to do the join.

See the following section for more information on applying transformations:

Data transformations