Skip to main content

DHTMLX Pivot. Dataset with aliases example

Demo of dataset aliases in DHTMLX Pivot. Map coded field values to human-readable labels using a prepareData function that transforms data before pivot initialization.

Live example​

prepareData(dataset, aliases, fields);
new pivot.Pivot("#pivot", {
    fields,
    data: dataset,
    config : {
        rows: ["continent", "form"],
		columns: ["year"],
		values: [
			"count(oil)",
			{ field: "oil", method: "sum" },
			{ field: "gdp", method: "sum" },
		],
    },
});

function prepareData(data, aliases, fields) {
    const dateFields = fields.filter(f => f.type == "date");
    
    data.forEach(item => {
        for (let field in aliases) {
            item[field] = aliases[field][item[field]];
        };
        dateFields.forEach(f => {
            const v = item[f.id];
            if (typeof v == "string") item[f.id] = new Date(v);
        })
    })
}
<!-- component container -->
<div id="pivot" style="width: 100%; height: 100%;"></div>

<!-- dataset -->

<script>
const aliases = {
      "form": {
        "1": "Republic",
        "2": "Constitutional monarchy"
      }
    };

    const dataset = [
      {
        "name": "Argentina",
        "year": 2015,
        "continent": "South America",
        "form":  "1",
        "gdp": 181.357,
        "oil": 1.545,
        "balance": 4.699,
        "when": "4/2/2015"
      },
      {
        "name": "Argentina",
        "year": 2017,
        "continent": "South America",
        "form":  "1",
        "gdp": 212.507,
        "oil": 1.732,
        "balance": 7.167,
        "when": "1/2/2017"
      },
      // ... 21 more items (see Live Editor for full data)
    ];
    
    const fields = [
      {
        "id": "name",
        "label": "Name",
        "type": "text"
      },
      {
        "id": "continent",
        "label": "Continent",
        "type": "text"
      },
      {
        "id": "form",
        "label": "Form",
        "type": "text"
      },
      {
        "id": "gdp",
        "label": "GDP",
        "type": "number"
      },
      {
        "id": "oil",
        "label": "Oil",
        "type": "number"
      },
      {
        "id": "balance",
        "label": "Balance",
        "type": "number"
      },
      {
        "id": "year",
        "label": "Year",
        "type": "number",
        "format": false
      },
      {
        "id": "when",
        "label": "When",
        "type": "date"
      },
    ];
</script>

Datasets from surveys, forms, or external APIs often store field values as numeric codes or short identifiers rather than readable labels. A "form" field might contain "1" for "Republic" and "2" for "Constitutional monarchy". Passing these coded values directly to the pivot results in cryptic row and column headers that are meaningless to end users.

DHTMLX Pivot works with the data array you provide, so you can preprocess the dataset before initialization to replace coded values with their aliases. A prepareData() function iterates over each record and substitutes field values using an alias mapping object. This approach keeps the pivot configuration clean while ensuring all headers display readable labels.

This example demonstrates alias mapping on a global economic dataset. The form field originally contains coded values ("1", "2") that are replaced with "Republic" and "Constitutional monarchy" before the pivot initializes. The function also converts date strings to Date objects for proper date handling. Rows group by continent and form of government, columns by year, with oil count, oil sum, and GDP sum as aggregated values.

Solution overview​

To use aliases, define a mapping object where each key is a field name and the value is an object mapping coded values to labels. Call prepareData() before creating the Pivot instance to transform the dataset in place. The function also handles date string conversion.

The aliases object is defined in the HTML section alongside the dataset:

Key points​

  • Preprocess before init: Call prepareData() before creating the Pivot instance to ensure all coded values are replaced with readable labels from the first render
  • Flexible alias mapping: The aliases object supports any number of fields and any value mapping - extend it with additional fields as needed without changing the pivot configuration
  • Date conversion included: The prepareData function handles both alias substitution and date string-to-Date conversion in a single pass over the dataset
  • Shorthand value syntax: The config uses "count(oil)" as shorthand for { field: "oil", method: "count" }, keeping the configuration concise when the default method name matches

API reference​

  • Pivot configuration: Properties for defining rows, columns, and value aggregations.
  • Pivot data: Configuration for providing the dataset to the Pivot component.
  • Pivot fields: Field definitions including type and label for each data column.

Additional resources​