Help Desk

Submit a ticket My Tickets
Welcome
Login

Advanced Tabular Transformation

In this exercise, we will fetch two different data sets for FACT and Mapping data. Once these files have been retrieved, we will transform the “Product” column from a code to a more convenient product name using the mapping file that contains a list of IDs and the corresponding product mapping. To perform this operation, we will use OneCloud’s Advanced Tabular Query command that leverages native SQL to perform the operation. Here are the sample datasets: 

Fact Data

YEAR,MEASURES,PRODUCT,MARKET,SCENARIO,PKG_TYPE,DATA
Jan,Sales,100-10,New York,Actual,Can,675.00
Feb,Sales,100-10,New York,Actual,Can,645.00
Mar,Sales,100-10,New York,Actual,Can,675.00
Apr,Sales,100-10,New York,Actual,Can,712.00
May,Sales,100-10,New York,Actual,Can,756.00
Jun,Sales,100-10,New York,Actual,Can,890.00
Jul,Sales,100-10,New York,Actual,Can,912.00
Aug,Sales,100-10,New York,Actual,Can,910.00
Sep,Sales,100-10,New York,Actual,Can,790.00
Oct,Sales,100-10,New York,Actual,Can,650.00
Nov,Sales,100-10,New York,Actual,Can,623.00
Dec,Sales,100-10,New York,Actual,Can,699.00

Product Mapping Data

ID,PRODUCT
100-10,Cola
100-20,Diet Cola
100-30,Caffeine Free Cola
200-10,Old Fashioned
200-20,Diet Root Beer
200-30,Sasparilla
300-10,Birch Beer
300-20,Dark Cream
300-30,Vanilla Cream

Step 1: Fetch Fact Data

  1. Create a new Chain and name it "Advanced Tabular Query" (Learn more).
  2. Add the first Command and select HTTP -> GET operation.

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

  3. Edit the "GET" Command with the following settings:

    ParameterSetting
    Command NameGet Fact Data
    URLSet to the Workspace variable "fact-data-with-prod-id"
  4. Save the Command.

Step 2: Fetch Product ID Mapping

  1. Add the second Command and select HTTP -> GET operation.

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

  2. Connect the "GET" Command to the "Get Fact Data" Command.

  3. Edit the "GET" Command with the following settings:

    ParameterSetting
    Command NameGet Prod Map
    URLSet to the Workspace variable "product-mapping"
  4. Save the Command.

Step 3: Add A Command to Preview the Data (optional)

  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.
    (Suggestion: drag the Command off to the side)

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

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

  5. Repeat the steps for the Command "Get Prod Map".

Step 4: Advanced Query

  • Select the Tabular Transformation -> Advanced Query Command.

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

  • Connect the "Get Prod Map" Command to the "Advanced Query" Command.

  • Edit the "Advanced Query" Command with the following settings:

    ParameterSetting
    Command NameAdvanced Query
    Add a table
    Set the File to be the Response of "Get Fact Data" and set the table name to "fact".
    Add a second table
    Set the File to be the Response of "Get Prod Map" and set the table name to "prod_map".
    Set the Query to the following

    Suggestion:
    Copy and Paste the text

    SELECT f.year, 
           f.measures, 
           m.product, 
           f.market, 
           f.scenario, 
           f.data 
    FROM fact f 
    JOIN prod_map m 
          ON f.product = m.id
    Input DelimiterComma
    Output DelimiterComma
    Preview Results
    Enabled
  • Save the Command

Step 5: 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. Things to note:
    a) The source fact data has product IDs (see the output of "Preview Fact Data").
    b) The output of the Advanced Query Command has product names versus product ids.
  5. During or after the run is complete, click on Smart Filter Command to see the output and confirm that the operation is working correctly with only "Massachusetts" and "Budget" records.

Step 6: Validate

Confirm that your data looks like the following:

YEARMEASURESPRODUCTMARKETSCENARIODATA 
JanSalesColaNew YorkActual675
FebSalesColaNew YorkActual645
MarSalesColaNew YorkActual675
AprSalesColaNew YorkActual712
MaySalesColaNew YorkActual756
JunSalesColaNew YorkActual890
JulSalesColaNew YorkActual912
AugSalesColaNew YorkActual910
SepSalesColaNew YorkActual790

What's Next?

> Advanced JSON Transformation

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.