How to Transform Data

In Workhelio, transforming data helps in maintaining data integrity, performing precise calculations, and generating meaningful insights. In this example, we will perform data conversion. The conversion involves changing the data from one form to another e.g. integer to string and vice-versa.

Steps to Convert Data Types

The following steps will help you to correct or change your data types. The example below will convert data from any data type to string:

1. Create a Dashboard

You can skip this step if you have a dashboard already created. However, if you have not created a dashboard, click on the plus (+) button on the top right of the screen.

Select the “Dashboard” option inside the modal that appears after clicking the plus button.

2. Name Your Dashboard

After selecting the dashboard, you will be taken to the dashboard page. On the top-left of the screen, edit the title of your dashboard to the title of your choice.

In this example, we will change the title to “Correct Data Type”. Click the save button on the top-right of the screen to save the title.

3. Edit the Dashboard to Create a New Chart

After saving the dashboard name, click the edit dashboard button to create a new chart.

The page will change and you will see a new button - “CREATE A NEW CHART”, click on it.

This will take you to a new page where you will select the chart type you want and build your preferred dashboard chart.

4. Choose Your Dataset and Chart Type

Choose a dataset to work with among the list of datasets in the select input options. We are going to select mock_employee_data as our option in this example.

Select the chart type. In this example, we are going to select the bar chart under the list of popular charts.

When you are done, click on the “CREATE NEW CHART” button at the bottom of the page.

5. Input Your Data for the Chart

Select and configure data for the x-axis and metrics, ensuring your visualizations accurately represent your data. Choose the CUSTOM SQL option under the X-AXIS modal and customize your data.

CAST(age AS STRING)

In the command above, the code will convert the age column to a string data type. After converting our age column to a string, we can equally concatenate another string to it.

CONCAT(CAST(age AS STRING), " years old")

The command above will concatenate “ years old” to the age column string. Write the command inside the CUSTOM-SQL and save it as shown below:

Choose your metrics and save.

6. Visualize and Complete the Chart Creation

Click the “CREATE CHART” button at the bottom of the page to visualize your new chart.

On the top-left of the page, add the name of the chart and click on the “SAVE” button at the top-right of the screen.

The save button opens a save modal where you can complete your chart creation.

List of Commands to Transform Data

Similar to how we convert column data above from one data type to another, there are other data transformations that we can carry out on Workhelio.

The following are commands to transform data in Workhelio:

1. Data Type Conversion

CAST(age AS STRING)

Where age is the data column and STRING is the data type you are converting to.

2. String Concatenation

CONCAT(CAST(age AS STRING), " years old")

3. Format Date

FORMAT_DATE('%x', `Order Date`)

Where Order Date is the data column for the date.

4. Format Time

FORMAT_TIME('%H:%M', PARSE_TIME('%I:%M %p', `ClockIn Time`))

Where ClockIn Time is the data column for the time.