Data transformations

Data can be transformed for many different reasons. Here are a few examples:

Correcting data errors
Unifying the data before doing aggregations (e.g. Sum)
Ability to join data from different tables

Data can be transformed in Peliqan in three different ways:

Using Formulas in the Peliqan Spreadsheet Editor
Using SQL queries in the Peliqan Query Editor
Using Low-code Python code in the Peliqan Data App editor

Transforming data in Peliqan

1. Using formulas

Add a new column to a table using column type “Formula”. Write a formula to transform data from other columns.

2. Using SQL queries

Add a new table of type “SQL query”, and write your own SQL query to create a new table. You can use any SQL statement to transform the data.

You can also define your own Database functions (Custom functions) which can be used in your SQL queries.

3. Using low-code Python

See Building Data Apps for more information. You can for example process the rows of a table in a Python script, and write the result to the same table (in other columns), or write to a separate table, or write to a report file (PDF, Google Sheet etc.).

Various Data Transformation scenarios

Here are examples of common data transformations for various use cases:

Convert numeric status fields to readable text

Merge data from multiple similar sources (e.g. SaaS)

Currency conversions

Mapping charts of accounts from different countries (accounting)

Overview of available transformations in Peliqan

ROW TRANSFORMATIONS
Filters	Apply filters to include/exclude rows based on various criteria
Advanced filters	Write SQL queries to filter your data
Remove duplicates	Coming soon
Edit rows	Edit data in a *spreadsheet-like interface*
COLUMN TRANSFORMATIONS
Arithmetic	Apply calculations using easy formulas
Case / if	Apply “if” logic using easy formulas
Comparison	Compare numbers, dates, text using easy formulas
Text manipulation	Apply text manipulation using easy formulas
Date manipulation	Apply date manipulation using easy formulas
SQL queries	Write SQL queries to apply any type of column transformation.
Custom SQL functions	Define your own custom functions in JS and use them in your SQL queries.
Obfuscate data	Obfuscate data, e.g. for GDPR reasons. Coming soon
TABLE TRANSFORMATIONS
Join	Join tables
Fuzzy join	Apply fuzzy join to combine tables without using a unique key. E.g. combine customer data from different sources.
Aggregations	Aggregate data using sum, avg, mean etc.
Union	Union two or more tables into a new table.
Advanced	Write SQL queries to apply any type of table transformation, using JOIN, UNION, GROUP BY statements etc.
Low-code Python	Use low-code Python scripts to process data and to create new tables and to perform writeback (Reverse ETL) to SaaS applications.