Data Transformation - Fuzible Software

Fuzible integrates a data transformation system which acts like a “PIVOT” function.

There are 3 transformation options that are accessible from the “Source” menu :

1. Hyperfile Arrays to Rows

It’s a specific Hyperfile transformation type. Hyperfile (HFSQL) is a database that makes use of array fields. When Hyperfile is retrieved with the ODBC driver, it produced as many fields as there are entries single array field.

For exemple, an Hyperfile array field called “myArrayField” with 8 entries will output 8 distinct fields : myArrayField_01, myArrayField_02…

That kind of transformation will automatically perform a pivot operation on those fields and create as many rows as there are array entries. That means that for an eight fields array, you’ll get 8 rows instead.

Hyperfile Settings. You can set the separator character (as for now, the driver makes use of the underscore) and you can also bypass the transformation if multiple array fields are detected in a single table, but with inconsistent sizes.

A concrete exemple :

To the left : the original data retrieved by the ODBC driver.
To the right, the produced transformation data.
An additional field has been created (IDX_COL) and serves as an index.

2. Pivot by Common root(s)

This kind of transformation will perform a field-to-row pivot by using a common root in column names.

It’s quite useful when you need to work with data that is presented in fields, which is not very convenient to query. This kind of transformation makes things a lot easier if you need to optimize your data schemas.

Common Root Settings. You can set one or more separator strings and you can also bypass the transformation if inconsistent field count between each common root is found.

In a few words, if I type “x” as my common root, any input field starting with “x” will be split and the engine will produce as many rows as there are fields starting with “x”. In addition, Fuzible will also add 3 more fields :

An “x” field, filled with the original content
An “x_lbl” field, filled with the pivoted field name
An “x_idx” field which acts as an index

A concrete exemple :

The Pivot by Coomon Root settings.

To the left, the original content from a CSV file.
To the middle, the query associated with that CSV file.
To the right, the produced transformation data.

As you can see, the original data source comes with field names that do not match with the common root (split). That’s why Fuzible relies on field aliases to perform the transformation (see the query).

3. Switch Rows and Columns

All is said. It will simply reverse your result set !

It can be useful in cases where you need to produce some reporting (sometimes, a row-to-column data presentation is easier to read)

There is a special “Add a column with label” setting that helps you to track down original field names associated with the pivoted values.

A concrete exemple :

Without “PROPERTIES”

To the left, the original data.
To the middle, the query associated with that data.
To the right, the produced transformation data.

With “PROPERTIES”