Friday, April 25, 2014

#FORMAT Mask Mapping for Target Values

It's good to see that FDMEE is being enhanced. For those who come from legacy FDM we were not used to new features since years!

As I described earlier in previous post, version 11.1.2.3.510 brings something new:
FORMAT MASK MAPPING

So what we have here? Think about the following example:

Source Account Value: 1001-2000-D123
Target Account Value: 11-Sales-123Cons

My source account has 3 different segments delimited by '-', and I want to define a mapping rule as follow (Power to the imagination!):
  • Ignore 2nd and 3rd characters of 1st segment
  • 2nd segment value starting with 2 indicates Sales so we have to replace the segment value by "Sales"
  • Third segment indicates subaccount. We have to ignore first character and add prefix Cons to subaccount code
So first mapping rule I can think would be to create a mapping script? maybe new #SQL script?

Yup, it could be but what about the following target value?
#FORMAT("?##?-Sales-#???Cons","-")

What the h*** is that? 假借字 形聲字
Chinese? Let's give a try in FMDEE!

My first Format Mask Mapping
My source file will be a very basic one:
I have highlighted source account value: 1001-2000-D123

Let's create a mapping rule using new Format Mask Mapping as follow:
Note: source value? 1001-2* is fine for this example.

Let's run the Data Load Rule and check target account value:
We got the desired target account: 11-Sales-123Cons

Time for explanations:

#FORMAT(<Format Mask>,<Segment Delimiter>)

  • #FORMAT - indicates mapping of type Format Mask in the target member
  • <Format Mask>
    • ? - Basically take from the source value the character in same position as ?
    • #Basically skip/drop the from the source value the character in same position as #
    • Character -  Include character (or string) as it is
    • * - Include all characters from source segment or source
  • <Segment Delimiter> - Character used to delimit segments in source and target members. It must be the same character for both source and target. Delimiter is optional as source value may not represent delimited segments.
#FORMAT("?##?-Sales-#???Cons","-")
  • 1st Segment mapping: ?##?
    • Take 1st and 4th source characters. Skip 2nd and 3rd > 11
  • 2nd Segment mapping: Sales
    • Replace the entire source segment by "Sales" > Sales
  • 3rd Segment mapping: #???Cons
    • Skip 1st character, take 2nd, 3rd, and 4th, and add suffix "Cons"  > 123Cons
 When special characters are used in conjunction there are some particularities:

Use Cases
When integrating with ARM (Account Reconciliation Manager), we need to load data to specific ARM profile. A profile is defined by a combination of segments (Ex: Account-Company-Department). Profiles are defined as part of the ARM configuration based on business requirements.

However this new feature can be also useful for non-ARM integration either using or not delimiters as part of target member definition.

More examples
Here you have few more examples:
Have a look to mapping rules 05 and 06.
Let's say that we have XXABCDEYY as source value.
Rule 05 would return ABCDEYY. Why? When using "*" before "#", FDMEE will take all remaining characters. In other words, "*" overrides "#".
Rule 06 fixes this issue and remove prefix and suffix successfully,
I leave source and target accounts so you can analyze each mapping applied: 

To conclude, #FORMAT completes the three # mapping types for FDMEE: #FORMAT, #SCRIPT, and #SQL
Has it limitations? I'm sure once people start using it more use cases will come up. In any case this a very good starting point for sure.

Enjoy!

No comments:

Post a Comment

Thanks for feedback!