How To

Using dataset operators in a mapping

Using a stage is a nice way to manipulate source sets with operatos like UNION, MINUS, INTERSECT, etc.

Step 1 : add the desired source table to the mapping
Step 2 : add a stage
Step 3 : on the stage, add Sets and map source columns to each set
Step 4 : in the expression editor of the stage, specify the desired expression, for example: [A] union ( [B] minus [C] )

Using Datastore Layers

In Mapping, hierarchical datastores nodes such as hierarchical files, XML files or JSON files can be displayed in 'Layers'.

A Layer is a view that allows to focus only on one level element of the hierarchical datastore, masking the others to be able to concentrate one at a time.

Add new comment

Binding variables to a sub-process

Actions like SQL Operation or Wait for Files can be used to bind their results to other actions, but binding to sub-processes is not actually possible directly in Stambia.

The solution is to bind the action to the delivery of the sub-process, with values bound as parameters. Here is an example of how to achieve it.

Note that in this case there will be separate sessions for Master and each of Slave processes, with separate contexts, variables etc.

The Main Process

mainProcess

In this process the delivery will be executed for each row returned by the SQL Operation.

We created a parameter, named CUSTOMER_ID in which we set the value retrieved from the source with the :{column}: syntax.

We will be able to use this parameter in the delivery, like shown below.

The sub-process

In this example, the sub-process contains a basic scripting action.

subProcess

We are publishing in it a variable at the root of the process, which contains the customer ID got from the bind:

__ctx__.publishVariable("~/Retrieved_ID","The Customer ID is ${~/CUSTOMER_ID}$");

We retrieve the value of the variable with ${<variable>}$ syntax.

Don't forget to publish the delivery of the sub-process:

publish

Add new comment

How to Pivot or Unpivot a table

Using Query

If you are working on a database that is capable of pivoting or unpivoting tables, you can simply write a query that makes the transformation and reverse it in Stambia.

Here is an example of how to do it using MSSQL Pivot operator.

Write a query that gives you the desired transformed table in MS SQL:

MS SQL Query

In the MS SQL metadata create a new query folder and a new query. Give it a name and paste the query:

Query in metadata

Save the metadata and the right click on query and choose Reverse from Actions:

Reverse query

Save the metadata again and now you can use the result of the query as a source in your mappings:

Reversed query in a mapping

Using Stages

In all other cases it is possible to make this kind of transformation in Stambia using stages.

Note if you do not have a database that you can use to create a stage you can use the internal H2 database included in the runtime (How to use internal H2 database?)

Example of pivot:

In this example we have a source table with a single line per quarter and we want to transform that to have a single line with 4 columns containing values per quarter.

Source unpivoted table

To do that we will need to add to our mapping four stages. Each Stage will be alimented with values from one quarter only (you need to add appropriate filter on each:

Pivot mapping

The stages should be then joined so they can be used to feed the columns of the target table:

Pivoted target table

Example of unpivot:

We can also imagine a situation where in the source of our mapping we have a table with quarterly values in columns and we want to feed a target table where each quarterly value will be represented in one line.

In this case we will need one stage with four sets:

Unpivot mapping

Each set represents a quarter with QNO value hardcoded and corresponding value mapped from source table. All sets are combined with UNION operator and can be used to feed the target “unpivoted” table.

Add new comment