Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.
The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…
Using a stage is a nice way to manipulate source sets with operatos like UNION, MINUS, INTERSECT, etc.
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.
You are working with tutorials demo database and you want to reset the databases to their default settings and data?
This article explains how to do it.
This article is now available on our new support portal:
https://support.semarchy.com/en/support/solutions/articles/43000658750-how-to-pivot-or-unpivot-a-table
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:
In the MS SQL metadata create a new query folder and a new query. Give it a name and paste the query:
Save the metadata and the right click on query and choose Reverse from Actions:
Save the metadata again and now you can use the result of the query as a source in your mappings:
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.
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:
The stages should be then joined so they can be used to feed the columns of the 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:
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.
Sub-Processes can be very usefull to separate and organize work.
Parameters, Metadata links, Metadata Variables, and more... can be set on it and this article explains how to reuse it in the Sub-Process.
Read more: Using parameters and variables set on a subProcess
