Transform Data

The relevant data that has been extracted into the DataEngine BaseTable cannot be used in its raw form. Hence, the data needs to be transformed to generate value-added and insightful data analysis reports. The extracted data in the DataEngine base table can be transformed using different query operations such as addition, subtraction, joining, aggregation, filtering etc. The transformed data is ideal for analysis purposes. For example, if you want to calculate the sum-of-sales revenue of products, you can perform addition operations to transform the data and obtain the sum of sales revenue.

The different query operations are categorized based on their nature:

Simple Operations

Aggregation

Sort

Filter

Join

Pivot

PeriodsToDate functions

These operations can be applied to the base tables by defining and executing a DataEngine query.

Defining a DataEngine Query

A DataEngine Query is defined using the query method of the Workspace class, which is belongs to the C1.DataEngine.Core assembly. This method accepts the following parameters:

• Query Name: If the name is specified, query result is stored on the disk in a table with the specified name.

• Query Columns: Columns could be already present in the base table or there could be new columns which are derived using query operations. Once a query has been defined, the Execute method of the Query class in used to execute the query.

The following code snippet defines a query to calculate the average salary of each department assuming that a base table named Employees exists in the workspace:

// Retrieve the base table for use in constructing queries
dynamic invoice = workspace.table("Invoices");
// Find the total payable amount(including the freight cost) corresponding to each order
dynamic query = workspace.query("TotalPayableAmounts", new
{
    Order_ID = invoice.OrderID,
    Product_ID = invoice.ProductID,
    TotalPayableAmount = Op.Add(invoice.ExtendedPrice, invoice.Freight)
});

And this is how the query is executed to create the result table:

//Execute the query
query.Query.Execute();

Once the query has been executed, the output is saved as a table in the workspace having the same name as that of the query.