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:

  1. Using Formulas in the Peliqan Spreadsheet Editor
  2. Using SQL queries in the Peliqan Query Editor
  3. 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

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
Apply calculations using easy formulas
Case / if
Apply “if” logic using easy formulas
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
Join tables
Fuzzy join
Apply fuzzy join to combine tables without using a unique key. E.g. combine customer data from different sources.
Aggregate data using sum, avg, mean etc.
Union two or more tables into a new table.
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.