Transforming Data

Addvanced EL Processor calculation transformation function

Calculation Transformation Function allows end user to write scripts to transform data. Input values represented as [F00N] Where N is a value number. For example calculation you can see above has got two input values [F001] and [F002]. Programming language used is very close to Pascal.

Calculation Examples:

 1. Strings Concatenation

 [F001] + [F002]

 Or

 Begin

Result:= [F001] + [F002];

End;

 2. Adding one value to another

Begin

Result:= StrToFloat([F001]) + StrToFloat([F002]);

End;

2. If Statement

Begin

If  [F002]='' then

Result:= 0

else If  [F002]=0 then

Result:= 0

Else

Result:= StrToFloat([F001]) mod StrToFloat([F002]);

End;

3. Variables

Var MyVariable : integer;

Begin

MyVariable:=10;
Result :=StrToFloat([F001]) mod MyVariable;

end;

List of functions used by Advanced ETL Processor Calculation Object

The list os contantly being expanded and might be slightly out of date

LowerCase(S)

LowerCase returns a string with the same text as the string passed in S, but with all letters converted to lowercase. The conversion affects only 7-bit ASCII characters between 'A' and 'Z'. To convert 8-bit international characters, use AnsiLowerCase.

AnsiUpperCase(S)

AnsiUpperCase returns a string that is a copy of S, converted to upper case.

AnsiLowerCase(S)

AnsiLowerCase returns a string that is a copy of the given string converted to lower case.

AnsiCompareStr(S1,S2)

AnsiCompareStr compares S1 to S2, with case sensitivity.

The return value is:

ConditionReturn Value

S1 > S2

> 0

S1 < S2

< 0

S1 = S2

= 0

AnsiCompareText(S1,S2)

AnsiCompareText compares S1 to S2, without case sensitivity. AnsiCompareText returns a value less than 0 if S1 < S2, a value greater than 0 if S1 > S2, and returns 0 if S1 = S2.

AnsiStrLIComp (S1,S2,MaxLen)

AnsiStrLIComp compares S1 to S2, without case sensitivity. If S1 or S2 is longer than MaxLen characters, AnsiStrLIComp only compares up to the first MaxLen characters.

The return value is:

ConditionReturn Value

S1 > S2

> 0

S1 < S2

< 0

S1 = S2 (up to MaxLen characters)

= 0

AnsiLastChar(S)

Call AnsiLastChar to obtain the last character in a string.

Trim(S)

Below is list of Miscellaneous Transformations supported by Advanced ETL Processor

ImageData Transformation FunctionDescription
Date Format Returns Date in ‘yyyy-mm-dd hh:nn:ss.fff' format.In order to load Date fields into target database they should be converted into ‘yyyy-mm-dd hh:nn:ss.fff' format
Current Date Returns Current Date in ‘yyyy-mm-dd hh:nn:ss.fff' format
Length Returns Length of String
Literal Populates field with a constant value
User Returns Current Windows User Name
Get Excel Cell Value Transformation function Get Excel Cell Value Returns Cell value of an Excel file
Get File Content Transformation function Get File Content Returns content of a single file
Splitter Splits data into number of values using Delimiter and Qualifier
Joiner Joins several values into one value delimited Delimiter and surrounded by Qualifier if specified
Accumulate Strings Transformation Function Accumulate Strings Accumulate strings data transformation function adds strings from previous records together,
Case Data Transformation Function Case Conditional Mapping
URL Location Transformation Function URL Location  Use URL location transformation to determine location parameters of URL such as Country, City, Region, Post Code, Street, latitude and longitude
IP Location Transformation Function IP Location  Use IP location transformation to determine location parameters of IP address such as Country, City, Region, Post Code, Street, latitude and longitude
URL Shortener Transformation Function URL shortnener  

Transforming data using regular expressions

 Advanced ETL Processor is able to check any data, for example date formats, post codes, phone numbers etc. It is also possible to write your own Regular expressions

 

Checking File format

Checking source data

 

What are the regular expressions?

Regular expression is a string that is used to describe or match a set of strings, according to certain syntax rules. Regular expressions are used by many text editors, utilities, and programming languages to search and manipulate text based on patterns. For example, Perl and Tcl have a powerful regular expression engine built directly into their syntax. Several utilities provided by Unix distributions—including the editor ed and the filter grep—were the first to popularize the concept of regular expressions.
"Regular expression" is often shortened to regex or regexp (singular), or regexes, regexps, or regexen (plural). Some authors distinguish between regular expression and abbreviated forms such as regex, restricting the former to true regular expressions, which describe regular languages, while using the latter for any regular expression-like pattern, including those that describe languages that are not regular. As only some authors observe this distinction, it is not safe to rely upon it. As an example of the syntax, the regular expression \bex can be used to search for all instances of the string "ex" that occur at word boundaries (signified by the \b). Thus in the string, "Texts for experts," \bex matches the "ex" in "experts," but not in "Texts" (because the "ex" occurs inside the word there and not immediately after a word boundary).

Source: wikipedia.

Below is a list of Date Validation Functions supported by Advanced ETL Processor

ImageData Validation FunctionDescription
Is Date Checks if the data is a Date
Is Date Between Checks if the data is a Date between two values
Is Date Less Than Checks if the data is a Date less than value
Is Date More Than Checks if the data is a Date more than value
Is Monday, Is Tuesday, Is Wednesday, Is Thursday, Is Friday, Is Saturday, Is Sunday, Is Today, Is Yesterday, Is Tomorrow, Is Weekend, Is Weekday Checks if the data is a Date value which belongs to specific day of week
Is January, Is February, Is March, Is April, Is May, Is June, Is July, Is August, Is September, Is October, Is November, Is December, Is Current Month, Is Last Month, Is Next Month Checks if the data is a Date value which belongs to specific month of a year
Is 1st Quarter, Is 2nd Quarter, Is 3rd Quarter, Is 4th Quarter, Is Current Quarter,Is Last Quarter, Is Next Quarter Checks if the data is a Date value which belongs to specific quarter of a year
Is Leap Year, Is Current Year, Is Last Year, Is Next Year Checks if the data is a Date value which belongs to specific year
Is Current Week, Is Last Week, Is Next Week Checks if the data is a Date value which belongs to specific week
Is Within Past Minutes, Is Within Past Hours, Is Within Past Days, Is Within Past Weeks, Is Within Past Months Checks if the data is a Date value within in past minutes etc.

Below is list of Number Transformations supported by Advanced ETL Processor

ImageData Transformation FunctionDescription
Round Rounding is the process of reducing the number of significant digits in a number. The result of rounding is a "shorter" number having fewer non-zero digits yet similar in magnitude. The result is less precise but easier to use.For example: 73 rounded to the nearest ten is 70, because 73 is closer to 70 than to 80.
Abs Returns absolute value of a number. Absolute value is always positive. For example -2 will give 2
Sign Returns Sign of numeric value -1 for negative values, 1 for positive values and 0 for 0
Add Transformation Function Add  Adds values together
Substract Transformation Function Substract  Substracts values from each other
Divide Transformation Function Divide  Devides values
Multiply Transformation Function Multiply  Multiplies values
Max Transformation Function Max  Calculates max value
Min Transformation Function Min  Calculates min value
If Integer Data Transformation Function If Integer Checks if the data is an Integer Value, if it is passes Success value to the next step otherwise passes Failure value
If Number Data Transformation Function If Number Checks if the data is an Numeric Value, if it is passes Success value to the next step otherwise passes Failure value
If Negative Data Transformation Function If Negative Checks if the data is a Negative Value, if it is passes Success value to the next step otherwise passes Failure value
If Positive Data Transformation Function If Positive Checks if the data is a Positive Value, if it is passes Success value to the next step otherwise passes Failure value
If Number Between Data Transformation Function If Number Between Checks if the data is a Numeric Value between two numbers, if it is passes Success value to the next step otherwise passes Failure value
If Number Less Than Data Transformation Function If Number Less Than Checks if the data is a Numeric Value less than specified Number, if it is passes Success value to the next step otherwise passes Failure value
If Number More Than Data Transformation Function If Number More Than Checks if the data is a Numeric Value more than specified Number, if it is passes Success value to the next step otherwise passes Failure value

We have 338 guests and no members online