Here is an example of complex transformation we created for one of our customers

Objective

To automate updating commodity catalogue

Overview

The company receives updates to the commodity catalogue as excel files on a daily basis. There are two types of updates PO and MTO. At the moment the commodity catalogue is stored as an Excel file, later it will be replaced with MS SQL Server Database

Business Rules

MTO file doesn’t have a project number, therefore, a single translation table would be used to identify source file x belong to y project in SQL DB

Note: Typically the MTO file is where we get size format issues fractions etc..

The rest of the rules apply to both of the files.

1)  Size fields must be converted as follows:

3/4=>.75
.875=>0.875
1 1/2 =>1.5

Leading and trailing spaces must be removed from sizes fields
If multiple fields are populated they must be reordered from small to large for example

Size1 = 10
Size2 = 3
Size3 = 4

Size1 = 3
Size2 = 4
Size3 = 10

2) Unit of measure is missing in MTO file needs to add on the fly. The rule is if quantity has decimal the use M for meters otherwise EA

3) Generating  material code

If Commodity code is populated use commodity code + sizes as PK, if size is not populated skip it EG:

CommodityCode_Size1_Size2_Size3 or
CommodityCode_Size1_Size2 or  
CommodityCode_Size1 or
CommodityCode

if no commodity codes and no sizes use tag number

4)  Validating data against the existing catalogue.

Exiting codes can have dashes or underscores in it, so it is important to validate both
CommodityCode _Size1_ Size2_ Size3 and CommodityCode -Size1-Size2-Size3
After that the logic as follows
if material code exists in the catalogue

Do not write to new catalogue import file

Otherwise

Write to new import file material code (commodity code with sizes or just tag number), Size, Size2 and Size3 (If not tag)
leave blank size 2 and 3 fields, if no values as blank,
Short Description or Description field if only one exists then duplicate in the blank column
if commodity exits pull ProductCategory value from it otherwise flag for the user to add in the new import file

Screenshots of actual transformation

This job was done for "GT Services experts in Materials Management, Order Managementment processess and reporting"