How to: Add Missing Values Using a Clean Keys Operator

Overview

You use the Clean Keys operator to fill in missing fields in your data set. In most applications, data is stored as arrays, where each item is organized into keys (or fields). However, some records might lack certain keys or values you need. The Clean Keys operator helps by inserting missing keys and assigning them the values.

Configuration

Imagine you’re working with a data set that tracks employee AWS (Amazon Web Services) certifications. To identify which employees still need certification, you can use the Clean Keys operator to add a new key, like needsCertification, to each record. That way, you to clearly label and act on employees who haven't been certified.

This example highlights a secondary capability of the Clean Keys operator. While it can be used to insert entirely new fields into your data set, its primary function is to populate missing values in keys that already exist.

These instructions assume you have a new module open, saved, and with a title.

Configure the Data Table Component

First, you'll add a Data Table component to store employee certification information.

1. In the Module Builder, drag and drop a Data Table component onto your canvas.

2. In the Property ID and Label Text fields, enter dtAWSCertData.

3. In the data table, enter the following:

   firstName	certification	accountValue	dateOfBirth
   Blair	aws solutions architect	1000000	07/23/90
   JJ	none	678900	02/26/89
   Jim	none	1000000	01/04/88
   Evan	aws developer	null
   Charlotte	aws security	5000000	02/01/91
   Amy	aws database	5439294
   Brian	aws machine learning
   Rupert	none

   

4. Click Save Component.

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, placing it below the dtAWSCertData Data Table component.

2. In the Property ID and Canvas Label Text fields, enter getCert.

3. Click Save Component.

Configure the Data Workflow Component

Next, you'll add a Data Workflow component that takes your data and adds the certification values.

1. Drag and drop a Data Workflow component onto your canvas, placing it below the getCert Hidden component.

2. In the Property ID and Canvas Label Text fields, enter dwfFilterMissingKeys.

   

Configure the Input Operator

1. Drag and drop an Input operator onto your Data Workflow canvas.

2. Configure the operator's Info window as follows:

   Setting	Value
   category	Input
   Component	dtAWSCertData
   required	Yes
   source	Default

Configure the First Console Operator

Next, you'll add a Console operator to display the status of your data before it passes through a Filter operator.

1. Drag and drop a Console operator onto your Data Workflow canvas.

2. Configure the operator's Info window as follows:

   Setting	Value
   Category	Console
   Label	Input Data

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

Configure the Filter Operator

Then, you'll add a Filter operator to remove certified employees from your data set.

1. Drag and drop a Filter operator onto your Data Workflow canvas.

2. Configure the operator's Info window as follows:

   Setting	Value
   category	Filter
   label	Filter Cert
   Do Not Sanitize Formula	(checked)
   Expression	certification="none"

3. Connect the output port (right) of the dtAWSCertData Input operator to the input port (left) of the Filter Cert Filter operator.

Configure the Second Console Operator

Next, you'll add another Console operator to display the status of your data after it passes through the Filter Cert Filter operator.

1. Drag and drop another Console operator onto your Data Workflow canvas.

2. Configure the operator's Info window as follows:

   Setting	Value
   category	Console
   label	Filter Result

3. Connect the output port (top right) of the Filter Cert Filter operator to the input port (left) of the Filter Result Console operator.

Configure the Clean Keys Operator

Now, you'll pass your data through a Clean Keys operator to add the Needs Certification key to the data set. The operator will also add Yes to the missing values in the new key.

1. Drag and drop a Clean Keys operator onto your Data Workflow canvas.

2. Configure the operator's Info window as follows:

   Setting	Value
   category	Clean Keys
   label	Fill Needs Cert
   Fill If	Missing
   Keys	needsCert
   Fill With	Yes

3. Connect the output port (right) of the Filter Cert Filter operator to the input port (left) of the Fill Needs Cert Clean Keys operator.

Configure the Third Console Operator

You'll add one last Console operator to display the status of your data after the Fill Needs Cert Clean Keys operator.

1. Drag and drop another Console operator onto your Data Workflow canvas.

2. Configure the operator's Info window as follows:

   Setting	Value
   category	Console
   label	Fill Qualify

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

Configure the Output Operator

1. Drag and drop an Output operator onto your Data Workflow canvas.

2. Configure the operator's Info window as follows:

   Setting	Value
   category	Output
   Component	getCert
   action	value

3. Connect the output port (right) of the Fill Needs Cert Clean Keys operator to the input port (left) of the getCert Output operator.

4. Save your Data Workflow.

Configure the ViewGrid Component

Next, configure a ViewGrid component to display your data set with the filled data values.

1. Drag and drop a ViewGrid component onto your canvas, placing it below the dwfFilterMissingKeys Data Workflow component.

2. In the Property ID and Label Text fields, enter vgNeedCertificates.

3. In the Inputs table, enter getCert and select Required.

4. In the Display table, enter the following:

   Display	Formula	Heading
   firstName		First Name
   certification		Certification
   accountValue		Account Value
   dateOfBirth		Date of Birth
   needsCert		Needs Certification

5. In the Action field, enter null.

   Using null prevents the dashboard from showing Submit buttons on each row.

   

6. Click Save.

Configure the Button Component

Lastly, configure a Button component to trigger your Data Workflow.

1. Drag and drop a Button component onto your canvas, placing it below the dtAWSCertData Data Table component.

2. In the Property ID field, enter btnNeedsCert.

3. In the Label Text field, enter Identify Who Needs Certification.

4. From the Action Type drop-down, select Event.

5. In the On Click field, enter dwfFilterMissingKeys.

   

6. Click Save Component.

7. Save your module.

Here's how the completed example looks in the Module Builder:

Preview the module in Express View and open the DevTools Console. Click the Identify Who Needs Certification button to display the data. Expand the three arrays of your Console operators. The original data table displays in the first Input Data Console. The updated data displays in the Filter Result Console array. Now, only uncertified employees display in the data table because you added a new data key to the filtered results using the Missing setting. In the Fill Qualify Console array, you'll see the added key needsCert display with the value of Yes.