Skip to main content
Skip table of contents

Indexima Calculated Fields

Introduction

Sometimes, the data in the base tables cannot be used directly and you must use an expression to change or "correct" it. For other use cases, you may want to create a calculation based on one or more fields. Instead of adding this expression for every visual in your BI tool, you can easily create a new calculated field in the dataset, and subsequently, use it in dashboards and visuals.

It is possible, at the creation table or later, to add columns with calculated fields.

Creating columns with Calculated Fields

Let's assume you have the following table nyc_yellow:

Let create 2 columns:

  • tripduration_minute = Drop off date/time - Pickup Date/time
  • Year_Month = trip pickup DateTime with the format YYYY-MM

Add columns

SQL
ALTER TABLE nyc_yellow ADD COLUMNS (tripduration_minute  as  round((unix_timestamp(Trip_Dropoff_DateTime)-unix_timestamp(Trip_Pickup_DateTime))/60) ); 
ALTER TABLE nyc_yellow ADD COLUMNS (Year_Month  as date_format(Trip_Pickup_DateTime,"YYYY-MM"));

After adding the 2 columns, you can display them:

The 2 columns tripduration_minute  and Year_Month are in fact virtual because the results of such columns are calculated on the fly. Genuine data from the database is not altered.

Creating hyperindex with Calculated Fields

You may include the calculated fields into hyperindexes:

SQL
ALTER TABLE nyc_yellow ADD INDEX (Year_Month,AVG(tripduration_minute));

As a result, the following query:

SQL
select Year_Month,Avg(tripduration_minute) AvgDuration from tmp_doc.nyc_yellow;

can be answered immediately,

by a hyperindex:

Deleting Calculated Fields

A drop command is required to remove columns with calculated fields.

Note that any index involving a calculated field to be deleted must be first deleted.

This is the case for our example.

  1. Delete the index:

    CODE
    ALTER TABLE nyc_yellow DROP INDEX nyc_yellow_0_1;
  2. Delete the 2 calculated fields:

    CODE
    ALTER TABLE nyc_yellow DROP COLUMN tripduration_minute ; ALTER TABLE nyc_yellow DROP COLUMN Year_Month ;
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.