# Expression Transformation

The *Expression* transformation object in Astera defines an expression or logic to process an incoming value (or values). As a result, it may return a new set of values which do not depend on any user-provided input data. Expressions can also be modified and used as variables for various other calculations.

The *Expression* Transformation object uses an expression as a logic to transform data. You can write an expression of your own or use different functions or operations from Astera’s extensive library of built-in functions, such as string manipulations, data conversion, date and time manipulation, etc. You can also perform various operations such as mathematical calculations and comparisons, etc. using the *Expression* transformation object.

### Video

{% embed url="<https://www.youtube.com/watch?v=nNbikH3WvcQ>" %}

### Use case

In this example, we have a sample dataset, *Customers*, stored in an Excel file. The address information in this source is split into multiple fields such as *Address*, *Region*, *Country*, and *PostalCode*. We want to concatenate the information in these fields and return it as full address in a new data field. For this, we will use *Expression* transformation object.

### How to work with Expression transformation

1. Retrieve your data in the dataflow by using one of the source objects from the Sources section in the Toolbox. In this example, we will work with an [*Excel Workbook Source*](https://documentation.astera.com/astera-data-stack-v10/dataflows/sources/excel-workbook-source).
2. Drag-and-drop the *Expression* transformation object from *Toolbox>Transformations>Expression* onto the designer.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FDDGqk1jijzONxcorPTYw%2Fgif-drag-and-drop.gif?alt=media\&token=39d502f3-1cb2-40db-b7b4-ba3bff9e2004)

3. Map the fields to be concatenated from the source object to the *Expression* transformation object. In this example, we have mapped the *Address, Region, City, Country* and *PostalCode* fields.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FGFBvQqJMKFe8h8LDJ7bk%2Fgif-mapping-fields.gif?alt=media\&token=ecb84efb-6e4b-4b17-8f27-1c72fc793a6c)

4. Now right-click on the *Expression* transformation object and select *Properties* from the context menu.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fxnpd29fs5snlQCZATZ6t%2F1.png?alt=media\&token=4b989652-ccb4-4ecf-972a-fac5b61be955)

5. This will open the *Layout Builder* window where you can add or remove fields and modify your layout.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FuJVEDNFMTobtPRLI6rim%2F2.png?alt=media\&token=04409a92-718c-4a67-9c96-a05870cbcf29)

These are the following options on the *Layout Builder* window:

* *Name:* This is where the field name is specified. You can change the name of existing fields if required.
* *Data Type:* Specifies data type of the mapped fields.
* *Input:* When checked, the field will be mapped as an input, with an input mapping port, to take data input from a source.
* *Output:* When checked, the field will be mapped as an output. If an expression is present, the expression will be applied to this output.
* *Variable:* Turns the field into a variable which can then be applied to other fields. These expressions are calculated first and then assigned to other expressions using it. Once a field turns into a *Variable*, it cannot be assigned for input or output mapping.
* *Expression:* This is where the expression used for modifying the field or group of fields is specified.

6. Since we want to write the address details from multiple fields into a single field, let’s create a new field named *Full\_Address*, and specify the *Data Type* as *String* and check the *Output* option.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FGfWfzt4PzaVCh5lsM8bd%2F3.png?alt=media\&token=88e37aa4-d9a7-4ce3-afeb-f7a35cf70fdf)

You can either write an expression directly in the Expression box in the *Layout Builder* window, or you can build an expression in the *Expression Builder*. To access the *Expression Builder*, click on ![img](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FdwKMcCCmfpugYK4aD4Kv%2Fclip_image001.png?alt=media\&token=d1f16c99-5f10-4a1b-8c65-3764f258de7c) option. An *Expression Builder* window will open.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FqaPei1DVy3vBs3gP4TIG%2F5.png?alt=media\&token=ddb2ae70-5cda-45e2-b380-dc451870be46)

You will find the following options in the *Expression Builder* window:

* *Functions:* An extensive library of built-in functions from where you can select any function according to your requirement.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FC4vnbWafYFIXJyumSEpN%2F11-1579000309010.png?alt=media\&token=03a44d1d-782d-4726-9b96-57195a8301e0)

* *Expressions:* Here, you can write an expression rule or choose one from the built-in functions in Astera.
* *Objects:* In this panel, you can find all the fields in your layout listed under the *Expression* node. You can double click on any field name to map it to your expression.

7. In this example, we can either use a concatenate function from the built-in functions or write an expression of our own to return the complete address information in a single field.

Address + ‘ ‘ + Region + ‘ ‘ + City + ‘ ‘ + Country + ‘ ‘ + PostalCode

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FMLuY04ustFsk6nKNWlzv%2F6.png?alt=media\&token=5ae33746-8665-4d12-8042-ed2ffb9ce10a)

{% hint style="info" %}
**Note:** Once you’ve built the expression, click on the *Compile* button to check whether or not the expression was compiled successfully. If the *Compile Status* is ‘Successful’, click *OK*.
{% endhint %}

9. You can now see your expression appear in the *Expression* field. Click *Next*.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FKGzSRzoBjBRQBY2hrXzM%2F7.png?alt=media\&token=43344a15-c491-4780-8329-930d66871f76)

10. A *General Options* screen will now open where you can add *Comments* and specify other *General Options*. Once you’re through with these general settings, click *OK*.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FlDmkvWAoKbSX1WlPijrA%2F8.png?alt=media\&token=5e0d1012-186a-41b5-8f28-ec4a4381979f)

*General Options screen*: This window shares options common to most objects in the dataflow.

* *Clear Incoming Record Messages*: When this option is checked, any messages coming in from objects preceding the current object will be cleared. This is useful when you need to capture record messages in the log generated by the current object and filter out any record messages generated earlier in the dataflow.
* *Do Not Process Records with Errors*: When this option is checked, records with errors will not be output by the object. When this option is off, records with errors will be output by the object, and a record message will be attached to the record. This record message can then be fed into downstream objects in the dataflow, for example, a destination file that will capture record messages or a log that will capture messages and collect statistics as well.
* In the *Comments* input section, you can write comments related to the object.

11. To preview the output, right-click on the *Expression* transformation object and select *Preview Output* from the context menu.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FT7DA6CfxYB8x3GASfm8j%2Fgif-preview-output.gif?alt=media\&token=c38ce6e9-aed2-4b03-a52e-ac55c62fd1d8)

Here’s a preview of the concatenated output:

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FC1FwyISwddrfKxcelZdU%2F9.png?alt=media\&token=d29a707c-5706-4b85-a1e0-0f829e292913)

12. You can now map your output to a destination or some other transformation in the dataflow . In this example, we will be writing our output to a [*Delimited File* destination](https://documentation.astera.com/astera-data-stack-v10/dataflows/destinations/delimited-file-destination).

You may rename your Destination object from the context menu options for this object. Here, we will rename it as *Full\_Address*.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fzy9J0qVWpgnBRyz3iIDN%2F10.png?alt=media\&token=aa1b2269-9f14-458d-b5de-585e73ada419)

This concludes using the *Expression* transformation object in Astera.