Before You Begin

This tutorial shows you how to standardize column values in a dataset in Oracle Analytics.

Background

Your data accuracy and quality suffer when a dataset column has misspelled names or multiple values names representing the same value, for example, Apple, Apple Computer Corp, or Apple Corporation.

What Do You Need?

Create a Dataset

In this section, you create a dataset with the merchant_spend spreadsheet.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then select Dataset.
  3. In Create Dataset, click Drop data file here or click to browse, select the merchant_spend.xlsx file, and then click Open.
  4. In Create Dataset Table from merchant_spend.xlsx, click OK. Click Save Save icon.
  5. In Save Dataset As, enter merchant_spend, and then click OK.
    6. Description of merchant_spend_ds.png follows
    Description of the illustration merchant_spend_ds.png

Review the Data in a Workbook

In this section, you create a workbook with the merchant_spend dataset to review the impact of inconsistent values in the MerchantName column.

  1. Click Create Workbook.
  2. In the Data Data pane icon pane hold down the Ctrl key, select MerchantName and OrderedQuantity, and then drag the data elements to the canvas.
  3. Select MerchantName and drag it to Color in the Grammar panel.
  4. In the visualization, click Menu visualization menu icon, select Sort By, select MerchantName, and then select from A to Z.


    Description of qnty_ordered_merchant.png follows
    Description of the illustration qnty_ordered_merchant.png
  5. Click Save save icon. In Save Workbook, enter merchant_name_wbk and click Save.

Standardize Column Values

In this section, you review the MerchantName column, and modify non-standard values to create consistent names for the values. When your standardized values such as in the MerchantName column, your visualization reflects accurate data.

  1. Click Go back Go back icon. On the Home page in the merchant_spend dataset, click Actions actions menu icon and select Open.
  2. Right-click the MerchantName column and select Replace Value List.
  3. In Replace Value, enter Dell for each Original Value row that starts with a variation of Dell, for example, Dell Computer Corporation, Dell Comp, and Dell Comp Corp.
  4. In Replace Value, enter Apple for each Original Value row that starts with a variation of Apple, for example, Apple Computer, Apple Inc., and Apple Computer Inc.
  5. In Replace Value, enter HP for each Original Value row that starts with a variation of Hewlett-Packard, for example, Hewlett-Packard Company, Hewlett-Packard Corp, and Hewlett-Packard Inc.
  6. In Replace Value, enter Adobe for each Original Value row that starts with a variation of Adobe, for example, Adobe Systems Incorporated, Adobe Inc., and Adobe Systems Incorporated.
  7. In Replace Value, enter CDW for each Original Value row that starts with a variation of CDW, for example, CDW Computer Centers Inc. and CDW Computers Inc.
    8. Description of replace_list.png follows
    Description of the illustration replace_list.png
  8. Click Add Step. Click Save save icon.
    9. Description of merchant_name_changes.png follows
    Description of the illustration merchant_name_changes.png
  9. Click Go back Go back icon.

Review the Updated Dataset

In this section, you open the merchant_name_wbk to view the MerchantName standardization changes and create another visualization.

  1. On the Home page in the merchant_name_wbk, click Actions actions menu icon and click Open.


    In the preview you can see the changes to standardize the values in the MerchantName column. The bar visualization reflects the standardization.

    Description of updated_visualization.png follows
    Description of the illustration updated_visualization.png
  2. In the Data Data pane icon pane, hold down the Ctrl key, select MerchantName and OrderedQuantity, and then drag them to the canvas.
  3. Select MerchantName and drag it to Color in the Grammar panel.


    Description of two_vizs.png follows
    Description of the illustration two_vizs.png

Learn More