This is a continuation of the discussion we started on Part 1 of this topic about Cloud Integration Templates, where we saw how to define field link rules using regular expressions. Refer to the post for other details. Let’s recap from the benefits of doing it:

 

  1. We did not have to specify rules for each individual field separately
  2. If the number of fields of each of these types changes in future these rules will still handle those.
  3. We have broken down the data structure into field sets of different logical types, for which we can define transformations specific to those

 

The template we used is as in the screenshot below. The rules described in the earlier post are about linking fields from the object “SQ” to the object “EXPRegEx”. Now we need to define transformations for each field set.

 

 

For that, the ability to perform #3 above is very important. Having defined the field links like this, if we then have to define transformations using the individual field names, it diminishes the benefits of using regex patterns for links.

 

Thankfully, templates allow a user to define transformations on regex patterns as well! Now we will see how we can do that.

 

For this we need to use what is called as “Macro definition” in expressions. We use a declaration field to identify all field name patterns, and then define pattern specific transformations on those.

 

In the screenshot below, the first field in the Expression is the declaration discussed above. It declares four patterns:

 

Name

Pattern

Description

QQ

Qty$

Any expression defined on the name “QQ” will be applied to all fields   with names ending with Qty

QY

Qtd$|Ytd$

Any expression defined on the name “QY” will be applied to all fields   with names ending with Qtd or Ytd

DF

Dt$|Date$

Any expression defined on the name “DF” will be applied to all fields   with names ending with Dt or Date

CC

Comment\d

Any expression defined on the name “CC” will be applied to all fields   with names having the text “Comment” followed by a digit

 

 

 

 

The names used for the patterns above are just for convenience and do not mean anything in particular. Now a quick word about those actual field names and expressions used in this Expression:

 

Let’s consider the field “OUT2_%DF%”. Since we have four date fields in the input the actual Custom Integration Task will create four fields with the values as below

 

Field

Expression

OUT2_CreatedDate

TO_DATE(CreatedDate,'yyyymmdd')

OUT2_LastModifiedDate

TO_DATE(LastModifiedDate,'yyyymmdd')

OUT2_FirstTransactionPostedDt

TO_DATE(FirstTransactionPostedDt,'yyyymmdd')

OUT2_MostRecentTransactionDt

TO_DATE (MostRecentTransactionDt,'yyyymmdd')

 

As you can see the task has “vertically expanded” the expression defined on the field pattern. In future if two more date fields are added to the input file structure, this will handle those automatically.

 

We do not have to use those prefixes like OUT2_ here, but doing that allows us to configure link rules or expressions downstream if necessary. For example, we can create a link rule that uses “^OUT2” in the pattern to link only these fields to another object.

 

Hope this example illustrates how you can identify and work on logical field sets using regex patterns through your data flow. Feel free to post your questions on the Informatica Cloud Developer Community as you try these!