Transforming Data for Charts

This guide explains how to transform data using RunJS and RunPy in ToolJet and visualize it using the chart component. The chart component in ToolJet enables the creation of various types of charts, with the option to integrate with Plotly for enhanced customization and advanced visualizations. While ToolJet provides the ability to connect to multiple databases, APIs, and data sources for integration, this guide will focus on using ToolJet DB to fetch the required data.

Plotting a Simple Pie Chart

To create a simple pie chart, the data has been stored in the table with the following structure in ToolJet DB:

Query to Fetch Data

1. Click on + Add button of the query manager at the bottom panel of the editor.
2. Select the ToolJet Database as the data source.
3. Select your table from the dropdown.
4. Select the List rows operation.
5. Click on the Preview button to preview the output or Click on the Run button to trigger the query.

This query will fetch the data from the ToolJet DB.

Transform the Data

To restructure the data into a format compatible with the chart component, we will be using RunJS transformation.

Create a new RunJS Query and add the following code:

await queries.getRevenueDetails.run(); 

let data = queries.getRevenueDetails.getData(); 

const courseCounts = data.reduce((counts, obj) => {
  if (obj.course) {
    counts[obj.course] = (counts[obj.course] || 0) + 1;
  }
  return counts;
}, {});

const courseData = Object.keys(courseCounts).map(course => ({
     x: course, 
     y: courseCounts[course] 
}));

return {courseData};

This query will calculate the number of each course and return an array of objects that can be utilized to plot the pie chart.

Plotting Pie Chart

1. Add a chart component from the component library available on right to the canvas.
2. Under Properties section select Pie as the chart type from the dropdown.
3. Under chart data section input {{queries.<Your RunJS Query Name>.data.courseData}} to input the data from the query.

Plotting a Line Chart with RunJS Transformation

To create the line chart, the data has been stored in the table with the following structure in ToolJet DB:

Query to Fetch Data

1. Click on + Add button of the query manager at the bottom panel of the editor.
2. Select the ToolJet Database as the data source.
3. Select your table from the dropdown.
4. Select the List rows operation.
5. Click on the Preview button to preview the output or Click on the Run button to trigger the query.

This query will fetch the data from the ToolJet DB.

Transform the Data

Create a new RunJS Query and add the following code:

const data = queries.getLinechartData.data;

const calculateAverage = (arr) => arr.reduce((sum, item) => sum + item.y, 0) / arr.length;

const regionData = data.filter(item => item.region !== "Asia");

const transformedData = regionData.map(item => {
  if (item.rdate && typeof item.rdate === 'string') {
    const dateParts = item.rdate.split('-'); 
    
    if (dateParts.length === 3) {
      const year = dateParts[2];   
      const month = dateParts[1];  
      return {
        x: `${year}-${month}`,    
        y: item.y && !isNaN(item.y) ? item.y : 0 
      };
    }
  }

  return { x: 'Invalid Date', y: 0 };
});

const validData = transformedData.filter(item => item.x !== 'Invalid Date');
const averageY = calculateAverage(validData);

const finalData = transformedData.map(item => ({
  x: item.x,
  y: item.y - averageY 
}));

return finalData;

Plotting Line Chart

1. Add a chart component from the component library available on right to the canvas.
2. Under Properties section select Line as the chart type from the dropdown.
3. Under chart data section input {{queries.<Your RunJS Query Name>.data}} to input the data from the query.

Plotting Candle Stick Chart Using Plotly

To create a candlestick chart, the data has been stored in the table with the following structure in ToolJet DB:

Query to Fetch Data

1. Click on + Add button of the query manager at the bottom panel of the editor.
2. Select the ToolJet Database as the data source.
3. Select your table from the dropdown.
4. Select the List rows operation.
5. Click on the Preview button to preview the output or Click on the Run button to trigger the query.

This query will fetch the data from the ToolJet DB.

Transform the Data

To restructure the data into a format compatible with the chart component, we will be using RunJS transformation.

Create a new RunJS Query and add the following code:

const dbData = queries.getCandlestickData.data;

if (!Array.isArray(dbData) || dbData.length === 0) {
  return { plotData: [] };
}

let dates = [];
let openPrices = [];
let highPrices = [];
let lowPrices = [];
let closePrices = [];

dbData.forEach(row => {
  dates.push(String(row.sdate)); 
  openPrices.push(row.open);    
  highPrices.push(row.high);      
  lowPrices.push(row.low);     
  closePrices.push(row.sclose);
});

const transformedData = [
  {
    x: dates,
    open: openPrices,
    high: highPrices,
    low: lowPrices,
    close: closePrices,
    type: 'candlestick'
  }
];

let result = {
  data: transformedData
};

return JSON.stringify(result)

Plotting Candlestick Chart

1. Add a chart component from the component library available on right to the canvas.
2. Enable use plotly JSON schema under Plotly JSON Chart Schema section.
3. Under JSON Description section input {{queries.<Your RunJS Query Name>.data}} to input the data from the query.

Plotting heatmap Chart Using Plotly

To create a heatmap chart, the data has been stored in the table with the following structure in ToolJet DB:

Query to Fetch Data

1. Click on + Add button of the query manager at the bottom panel of the editor.
2. Select the ToolJet Database as the data source.
3. Select your table from the dropdown.
4. Select the List rows operation.
5. Click on the Preview button to preview the output or Click on the Run button to trigger the query.

This query will fetch the data from the ToolJet DB.

Transform the Data

To restructure the data into a format compatible with the chart component, we will be using RunPy transformation.

Create a new RunPy Query and add the following code:

import pandas as pd
import json

data_raw = queries.fetchHeatmapData.getData()

data = data_raw.to_py() if hasattr(data_raw, 'to_py') else list(data_raw)

df = pd.DataFrame(data)
heatmap_data = df.pivot(index='y', columns='x', values='value')

x_labels = [f"Column {i}" for i in heatmap_data.columns.tolist()] 
y_labels = [f"Row {i}" for i in heatmap_data.index.tolist()]

output = {
  "data": [
    {
      "z": heatmap_data.values.tolist(),  
      "x": x_labels,
      "y": y_labels,
      "type": "heatmap"
    }
  ]
}

output_str = json.dumps(output)

output_str

Plotting Heatmap Chart

1. Add a chart component from the component library available on right to the canvas.
2. Enable use plotly JSON schema under Plotly JSON Chart Schema section.
3. Under JSON Description section input {{queries.<Your RunJS Query Name>.data}} to input the data from the query.