Help Desk

Submit a ticket My Tickets
Welcome
Login

Basic Data Transformation Operations

In this exercise, we will fetch data from a URL using the HTTP BizApp and then perform the various operations:

  • Remove the column "PKG_TYPE"
  • Replace the string "New York" with "NY"
  • Create a column to hold the "Fiscal Year" of "2020"
  • Create a new "PERIOD" column by joining the "YEAR" and "Fiscal Year" columns
  • Add a unique Row ID
  • Create two separate outputs by filtering on "Actual" and "Budget" records

Step 1: Fetch Sample Data

  1. Create a new Chain and name it "Basic Transformations" (Learn more).
  2. Add the first Command and select HTTP -> GET operation with the following settings:

    (Hint: use the Find tool to search for "HTTP" and then search for "GET".)

    ParameterSetting
    Command NameGet Fact Data
    URLUse the Workspace variable "fact-data"
  3. Save the Command.

Step 2: Add A Command to Preview the Data

  1. Select the File Utilities -> List File Content Command.

    (Hint: use the Find tool to search for "File Utilities" and then search for "List File Content")

  2. Connect the "Get Fact Data" Command to the "List File Content" Command.

  3. Edit the "List File Content" Command with the following settings:

    ParameterSetting
    Command NamePreview Data
    Preview Lines10
    File NameResponse from the "Get Fact Data" Command Output
  4. Save the Command.

Step 3: Strip the PKG_TYPE Column

  1. Select the Tabular Transformation -> Column Filter Command.

    (Hint: use the Find tool to search for "Tabular Transformation" and then search for "Column Filter")

  2. Connect the "Preview Data" Command to the "Column Filter" Command.

  3. Edit the "Column Filter" Command with the following settings:

    ParameterSetting
    Command NameStrip PKG_TYPE
    Input FileResponse from the Command "Get Fact Data"
    Preview ResultsEnabled
    DelimiterComma
    Pattern typeExact
    Inverse OptionDisabled
    Pattern"PKG_TYPE"
  4. Save the Command.

Step 4: Replace "New York" with NY

  1. Select the Tabular Transformation -> Find and Replace Command.

    (Hint: use the Find tool to search for "Tabular Transformation" and then search for "Find and Replace")

  2. Connect the "Strip PKG_TYPE" Command to the "Find and Replace" Command.

  3. Edit the "Find and Replace" Command with the following settings:

    ParameterSetting
    Command NameNew York --> NY
    Input FileCommand Output from the command "Strip PKG_TYPE"
    Preview ResultsEnabled
    DelimiterComma
    Find Pattern"New York"
    Pattern typeExact
    Replacement Value"NY"
    Replace matches onlyDisabled
    Case Insensitive Disabled
    Column3
    (This will isolate the operation to be only in the "MARKET" column. Note: the column count is zero-based)
  4.  Save the Command.

Step 5: Add Fiscal Year Column

  1. Select the Tabular Transformation -> Insert Column Command.

    (Hint: use the Find tool to search for "Tabular Transformation" and then search for "Insert Column")

  2. Connect the "New York --> NY" Command to the "Insert Column" Command.

  3. Edit the "Insert Column" Command with the following settings:

    ParameterSetting
    Command NameInsert Fiscal Year
    Input FileCommand Output from the command "New York --> NY".
    Preview ResultsEnabled
    DelimiterComma
    Header Text"Fiscal Year"
    Data Value"2020"
    Insert Index"1"
  4. Save the Command.


Step 6: Create a New Column by Joining Two Others

  1. Select the Tabular Transformation -> Join Columns Command.

    (Hint: use the Find tool to search for "Tabular Transformation" and then search for "Join Columns")

  2. Connect the "Insert Fiscal Year" Command to the "Join Columns" Command.

  3. Edit the "Join Columns" Command with the following settings:

    ParameterSetting
    Command NameJoin Cols (PERIOD)
    Input FileCommand Output from the command "Insert Fiscal Year"
    Preview ResultsEnabled
    DelimiterComma
    Joined column indexleave blank
    (it will default to 0)
    Match Pattern TypeExact
    Match Pattern"YEAR,Fiscal Year"
    (case sensitive and no space between "Year" and "Fiscal")
    Join Column Header"PERIOD"
    Join Text"-"
    DiscardEnabled
    (to remove the columns that were joined)
  4. Save the Command.

Step 7: Add a Unique Row ID

  1. Select the Tabular Transformation -> Add Row Numbers Command.

    (Hint: use the Find tool to search for "Tabular Transformation" and then search for "Add Row Numbers")

  2. Connect the "Join Cols (PERIOD)" Command to the "Add Row Numbers" Command.

  3. Edit the "Add Row Numbers" Command with the following settings:

    ParameterSetting
    Command NameAdd Row Numbers
    Input FileCommand Output from the command "Join Cols (PERIOD)"
    Preview ResultsEnabled
    DelimiterComma
  4. Save the Command.

Step 8: Group Transformation Commands

  1. Drag "New York --> NY" onto "Strip PKG_TYPE" to create a Command Group.
  2. Drag "Insert Fiscal Year" onto the newly created Command Group.
  3. Drag "Join Cols (PERIOD)" onto the Command Group.
  4. Drag "Add Row Numbers" onto the Command Group.
  5. Hover your mouse over the Command group and click the pencil icon to edit the Command Group.
  6. Rename the Command Group to "Transformation".
  7. Save the Command Group.
  8. Reposition the Command Group to line up the Commands.

Step 8: Filter data based upon Actual and Budget Records

  1. Select the Tabular Transformation –> Filter Rows Command.
    (Hint: use the Find tool to search for "Tabular Transformation" and then search for "Filter Rows")
  2. Connect the Command Group to the "Filter Rows" Command.
  3. Edit the "Filter Rows" Command with the following settings:
    ParameterSetting
    Command NameFilter Rows (Budget)
    Input FileCommand output from "Add Row Numbers"
    Preview ResultsEnabled
    DelimiterComma
    Find Pattern"Budget"
    Match pattern typeExact
    Case InsensitiveDisable
    Search Columns5
    InverseEnabled
  4. Save the Command.
  5. In the Studio view, copy the Command "Filter Rows (Budget)"
    (Select the Command and a toolbar will appear)
  6. Edit the copied Command with the following settings:
    ParameterSetting
    Command NameFilter Rows (Actual)
    Find Pattern"Actual"
  7. Save the Command.

Step 9: Publish and Run the Chain

Before the Chain can be run, it needs to be published. After the Chain is published, it can be run.

  1. Publish the Chain, by clicking the PUBLISH button on the upper bar.
  2. Navigate to the Execute area of OneCloud by clicking on the EXECUTE button on the upper bar.
  3. Run the Chain.
  4. During or after the run is complete, click on various Commands to see the output.
  5. Confirm that the respective filters are working correctly with "Budget" and "Actual" data.

Step 10: Validate

Confirm that your data looks like the following:

Budget Data

Actual Data


What's Next?

> Smart Filters

B
Brian is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.