Skip to main content
To transform incoming numeric data, such as currency, churn score and lifetime transaction value, from commas (US format) to decimals (European format) and remove any leading or trailing spaces, use the following transformations:
  • PARSE-AS-CURRENCY
  • TRIM, LTRIM and RTRIM

​
PARSE-AS-CURRENCY

The PARSE-AS-CURRENCY is a directive for parsing a currency value that is a string representation of locale currency into a number. Syntax parse-as-currency <source> <destination> [<locale>] PARSE-AS-CURRENCY can be used to parse a string representation of currency into a number. If locale is not specified in the directive, a default of ‘en_US’ is assumed. When the directive is unable to parse the currency, an error record is generated. Following are few examples of parsing currency into number:
Code
parse-as-currency :src :dst
parse-as-currency :src :dst 'en_US'
parse-as-currency :src :dst 'en_IE'
parse-as-currency :src :dst 'pl_PL'
parse-as-currency :src :dst 'ca_ES'
parse-as-currency :src :dst 'es_ES'
parse-as-currency :src :dst 'es_CO'
parse-as-currency :src :dst 'de_CH'
parse-as-currency :src :dst 'en_ZA'
parse-as-currency :src :dst 'en_GB'
parse-as-currency :src :dst 'fr_BE'

​
TRIM, LTRIM and RTRIM

The TRIM, LTRIM, and RTRIM directives trim whitespace from both sides, left side or right side of a string values they are applied to. Syntax
Code
trim <column>  
ltrim <column>  
rtrim <column>
The directive performs an in-place trimming of space in the value specified by the <column> The trim directives honors UNICODE space characters. Following are the characters that are recognised as spaces by TRIM, LTRIM and RTRIM.
CharacterDescription
\tCharacter tabulation
\nLine Feed (LF)
\u000BLine Tabulation
\fForm Feed (FF)
\rCarriage Return (CR)
” “Space
\u0085Next line (NEL)
\u00A0No Break Space
\u1680OGHAM Space Mark
\u180EMongolian Vowel Separator
\u2000EN Quad
\u2001EM Quad
\u2002EN Space
\u2003EM Space
\u2004Three Per EM space
\u2005Four Per EM space
\u2006Six Per EM space
\u2007Figure Space
\u2008Puncatuation Space
\u2009Thin Space
\u200AHair Space
\u2028Line Separator
\u2029Paragraph Separator
\u202FNarrow No-Break Space
\u205FMedium Mathematical Space
\u3000Ideographic Space

​
Use Case: Generating a Monthly Financial Report from Raw Transaction Data

Suppose, you are working on generating a monthly financial report from raw transaction data collected from a retail store. The data includes fields related to coupon redemptions, ticket amounts, churn scores, and customer lifetime values. To prepare this data for accurate reporting, you need to clean and format it using specific directives.

​
Steps to Process the Data

  1. Trim Whitespace: Remove any leading or trailing whitespace from the relevant fields.
  2. Parse as Currency: Format the numerical values as currency in the Belgian French locale (fr_BE).
  3. Find and Replace: Standardise decimal notation by replacing commas with periods.

​
Directives for Data Transformation

In this case, in the mapping screen, create a custom field for which transformation is required and enrich it by adding a combination of directives as per your requirement. In this example the following directives are used to transform, amountRedeemedCoupons, averageAmountTicket and totalAmountTicket.
Code
trim :amountRedeemedCoupons
parse-as-currency :amountRedeemedCoupons :amountRedeemedCoupons 'fr_BE'
trim :averageAmountTicket
parse-as-currency :averageAmountTicket :averageAmountTicket 'fr_BE'
find-and-replace churnscore s/,/./g
find-and-replace lifetimeValue s/,/./g
trim :totalAmountTicket
parse-as-currency :totalAmountTicket :totalAmountTicket 'fr_BE'
Below are details of how each directive functions:
DirectiveDescription
trim :amountRedeemedCouponsRemoves the space at the start and the end of the amountRedeemedCoupons.
parse-as-currency :amountRedeemedCoupons :amountRedeemedCoupons 'fr_BE'Converts the currency value of amountRedeemedCoupons to the standard currency value using the ‘fr_BE’ format.
trim :averageAmountTicketRemoves the space at the start and the end of the averageAmountTicket.
parse-as-currency :averageAmountTicket :averageAmountTicket 'fr_BE'Converts the currency value of averageAmountTicket to the standard currency value using the ‘fr_BE’ format.
find-and-replace churnscore s/,/./gFinds commas in churnscore and replaces them with periods.
find-and-replace lifetimeValue s/,/./gFinds commas in lifetimeValue and replaces them with periods.
trim :totalAmountTicketRemoves the space at the start and the end of the totalAmountTicket.
parse-as-currency :totalAmountTicket :totalAmountTicket 'fr_BE'Converts the currency value of totalAmountTicket to the standard currency value using the ‘fr_BE’ format.
Last modified on February 26, 2026