DataEngine for .NET Standard | ComponentOne
Visual ETL & Data Analysis with Workbench / Transform Data
In This Topic
    Transform Data
    In This Topic

    Transforming data in the DataEngine BaseTable helps in creating insightful data analysis reports. A user can apply different query operations such as Range Expressions, Aggregation, Filtering etc. on the data. In the section below, you will learn in detail about transformation operations in DataEngine Workbench.

    Range Expressions and Filtering

    The user can also limit the scope of a query by specifying one or more range expressions. The following example depicts how a user can add a range to a join query. Note that you can also use ranges with single-table queries.

    Follow the steps below to create query result with range expressions.

    1. Add a query by clicking the  Add query  in the Queries table. Fill the cells in the grid under the TABLES tab as given in the image.

      Fill table

    2. Fill the cells in the grid under the COLUMNS tab as given in the image.

      Fill cells

    3. Fill the cells in the grid under the RANGES tab as given in the image. Here, as you can observe, we have filtered the Value 'Beverages' in the CategoryName Column using the Equal Operation.

      Fill cells under RANGES

    4. In the REVIEW tab, add the query name as 'BeverageTotals'. Click the CREATE button. The newly added query appears in the Queries panel. Display the results by clicking its grid icon.

      Users can also customize the Query result set. This is explained in Customize Base Table and Query Data topic.

      Note that the user can copy the JSON string and use it in application code with the CreateQueryFromJsonString method of QueryFactory class.

    Date and Time Aggregates

    Let's say the base table data contains date or time columns. In such a case, you can use date-specific functions in query definitions to aggregate by year, month, etc.

    Follow the steps below to create query result with date and time aggregates.

    1. Click the expand button on the right of the Queries panel. Select Invoices in the top left grid cell, under the Tables column in the TABLES tab. Click the right arrow button.

      Aggregate

    2. Fill the cells of the grid in the COLUMNS tab as given in the image below:

      Fill the cells in grid

      Note that when a row contains a date or time column, the Operation column dropdown contains two groups of values, Aggregate and Date/Time.

    3. Click the REVIEW tab, and enter 'CountryTotalsByYear' as the query name. Click the CREATE button.

      Enter query name

      The text box at the bottom of the page is not editable, but shows the JSON data that will be used to create the query. Note that the user can copy the JSON string and use it in application code with the CreateQueryFromJsonString method of QueryFactory class.

    4. Expand the newly added node under Queries. Click the grid icon to display the results.

      Users can also customize the Query result set. This is explained in Customize Base Table and Query Data topic.