Clean Keys Operator

Overview

Sometimes your data sets have missing or empty values. The Clean Keys operator lets you fill in these empty or missing values with a number or string.

When you enter or bring data into your module, your application stores it in an array. An array organizes your data into numbers and keys. The Clean Keys operator uses key names to create new keys and fill their values, or fill values in an existing key.

The operator has 3 options for filling in your data values. Let's take a look at these options: 

  • Missing: Fills values for existing and non-existing keys. Adds a key if it doesn't exist in your data and fills all values for that key.

  • Empty: Creates values in your data for existing keys.

  • Missing or Empty: Adds missing keys and values for existing and non-existing keys in a data set.

For this article, let's say you have a data set that records employee certifications. But, the data has empty certification values that you want to fill. You'll use the Clean Keys operator to fill these values.

You’ll find the Clean Keys operator under the Table group at the left of the Data Workflow canvas.

Similar Data Workflow Operators

The Clean Keys operator is similar to the Rename Field and Clean Object operators. Here are the operators compared: 

  • Clean Keys operator: Fills in missing or empty values in a data set.

  • Rename Field operator: Changes the name of fields in a data set.

  • Clean Object operator: Removes or converts unwanted data values, including null values, in a data set.

What You'll Learn

In this article, you'll learn:

About the Info Window

Here's what the Clean Keys operator looks like, along with its Info window:

And here's a breakdown of each setting in the Info window:

Setting

Description

Category

Grayed out and non-adjustable setting indicating the operation type.

Label

Sets the label for your operator. The label you enter here shows beneath your operator on your Data Workflow canvas.

This field is optional, but set a label if you use more than one of the same operator type. A label helps you identify your operators without opening any Info windows.

Fill If

This drop-down lets you select which values you want to fill.

  • Missing: Fills all values for keys that exist and don't exist in your data. This option creates a key if it's missing from your data set. It also fills all values under the generated key with the value from Fill With.

NOTE  If you add a value to your data set and then delete it, the value gets a key and becomes Empty. Adding a space or clicking into a data cell also changes it from Missing to Empty.

  • Empty: Fills data values that have a key but are empty.

  • Missing or Empty: Fills values for data sets that are missing a key or that have a key and are missing values.

Keys

Enter your key names here for the following: 

  • For Missing keys, add the key name you'd like to add.

  • For Empty values, add the key you'd like to fill the empty values for.

NOTE  You can enter more than one key name in this field.

Fill With

Names your empty or missing values. If your key is Missing and doesn't exist on your data set, this field adds all data under the new key field. If your field is Empty, this field fills the values under the key name you entered.

Adding a Clean Keys Operator

Let's see how the Clean Keys operator works by creating an application. You'll start by setting up a data table that records employee AWS (Amazon Web Services) certification. Your data will have empty certification values you'll want to fill. The certification key exists, so you'll use the Empty option to fill in the empty values on your data. Then, you'll display the data on an updated data table for the end-user to see.

Here's how your module will look in the Module Builder: 

Here's how a completed use case will look in the Express View: 

What You'll Need

To set up this use case, you’ll need:

  • 1 Data Table component

  • 1 Hidden component

  • 1 ViewGrid component

  • 1 Data Workflow component

To set up your Data Workflow, you’ll need:

  • 1 Input operator

  • 2 Console operators

  • 1 Clean Keys operator

  • 1 Output operator

Configure the Data Table Component

First, you'll add a Data Table to hold the data with the missing certification values.

1. In the Module Builder, drag and drop a Data Table component onto your canvas.
2. Enter dtAWSCertData in the Label and Property Name fields.
3. In the data table, enter the following information. When you create the blank spaces in the data table, click into the cells until your cursor appears. You'll want to do this for all blank values except for JJ's certification.

NOTE  JJ doesn't have a key for certification in this data set. As a reminder, adding values to a blank cell adds a key and creates a missing value. When you add JJ, you'll want to avoid adding any missing values, like adding a space or clicking into a data cell.

firstName certification accountValue dateOfBirth
Blair aws solutions architect

1000000

07/23/90

JJ

 

678900

02/26/89

Jim

 

1000000

01/04/88

Evan

aws developer

null

 

Charlotte

aws security

5000000

02/01/91

Amy

aws database

5439294

 

Brian

aws machine learning

 

 

Rupert

 

 

 

4. Click Save.

Configure the Hidden Component

Then you'll add a Hidden component to store the output from your Data Workflow.

1. Drag and drop a Hidden component onto your canvas under your Data Table.
2. Enter addedCert in the Property ID and Canvas Label Text fields.
3. Click Save.

Configure the Data Workflow Component

Next, you'll add your Data Workflow component. This component adds the empty certification values to your Data Table.

1. Drag and drop a Data Workflow component onto your canvas. Place your Data Workflow below the Hidden component.
2. Enter dwfFillKeysIfEmpty in the Canvas Label Text and Property Name fields.

Configure the Input Operator

1. Drag and drop an Input operator onto your Data Workflow canvas.
2. Configure the Input operator's Info window as follows:
Setting Value

Category

Input

Component

dtAWSCertData

Required

Yes

Source

Default

Configure the First Console Operator

You'll add a Console operator next. You'll use this Console to show the status of your dtAWSCertData Input data.

1. Drag and drop a Console operator onto your Data Workflow canvas.
2. Configure the Console operator's Info window as follows:

Setting

Value

Category

Console

Label

Input Data
3. Connect the output port (right) of the Input operator to the input port (left) of the Console operator.

Configure the Clean Keys Operator

Then you'll add the Clean Keys operator. You'll use this operator to fill the empty values in the certification key with the none value.

1. Drag and drop a Clean Keys operator onto your Data Workflow canvas.
2. Configure the Clean Keys operator's Info window as follows:

Setting

Value

Category

Clean Keys

Label

Fill Certification

Fill If

Empty

Keys

certification

Fill With

none
3. Connect the output port (right) of the Input operator to the input port (left) of the Clean Keys operator.

Configure the Second Console Operator

Then you'll add another Console operator. This Console operator shows you the progress of the Clean Keys operator.

1. Drag and drop a Console operator onto your Data Workflow canvas.
2. Configure the Console operator's Info window as follows:

Setting

Value

Category

Console

Label

Fill Certification
3. Connect the output port (right) of the Clean Keys operator to the input port (left) of the Console operator.

Configure the Output Operator

1. Drag and drop an Output operator onto your Data Workflow canvas.
2. Configure the Output operator's Info window as follows:

Setting

Value

Category

Output

Component

addedCert

Action

value
3. Connect the output port (right) of the Clean Keys operator to the input port (left) of the Output operator.
4. Save the Data Workflow.

Configure the ViewGrid Component

You'll need a way to show the changes you've made to the end-user. Use the ViewGrid component to show your data set with the filled data values.

1. Drag and drop a ViewGrid component onto your canvas under your Data Workflow.
2. Enter vgCertificates in the Label and Property Name fields.
3. Enter null in the Action field.
4. In the Inputs table, enter addedCert in the ID column.
5. In the Display table, enter the following: 

Display

Formula

 Heading

firstName

 

First Name

certification

 

Certification

accountValue

 

Account Value

dateOfBirth

 

Date of Birth

6. Click Save.
7. Save your module.

Just like that, you're done! Now that you're finished, you can preview the module in Express View. Start by opening the DevTools Console. In the Console, you'll see updates from your 2 Console operators. When you expand the arrays, you'll see none added in the empty certification key values.

You'll also see that JJ's certification doesn't exist on the ViewGrid or in the input data Console. When you created JJ's data, you skipped over that data cell. Because of the skip, JJ's certification data is Missing and doesn't have a key.

Changing a Clean Keys Operator's Settings

You can revisit and make changes to this operator.

1. Click the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator to open its Info Window.
4. Make changes to the operator's settings as needed.
5. Click Save.
6. Save your module.

Copying a Clean Keys Operator

You can make a copy of your operator using familiar keyboard settings. You can copy and paste an exact copy of your operator, matching all settings.

1. Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator you want to duplicate.
4. On your keyboard, press Command + C (Mac OS) or Ctrl + C (Windows/Linux) to copy the operator.

TIP  You can copy more than one operator at a time. Hold Command or Ctrl on your keyboard and click all the operators you want to copy. Follow the rest of the steps as usual.

5. On your keyboard, press Command + V (Mac OS) or Ctrl + V (Windows/Linux) to paste the copied operator(s) to the Data Workflow canvas.
6. Click Save.
7. Save your module.

TIP  Did you know you can copy an operator and paste it into a different Data Workflow? You'll use the same steps outlined above.

Removing a Clean Keys Operator

Lastly, you can delete this operator from your Data Workflow canvas. You can also use these same steps to delete a connection between two operators.

1. Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator you want to delete.
4. On your keyboard, press Delete.

NOTE  Once you delete this operator, make sure to connect your remaining operators. If your Data Workflow path doesn't end with an Output or Console operator, your Data Workflow won't work. So, once you delete an operator, make sure to update any remaining paths to end at an Output or Console operator.

5. Click Save.
6. Save your module.

Lab

You can view this complete use case here: https://training.unqork.io/#/form/6047c9a15746b2024161b472/edit.