¶ Description
Filter rows of an input table.
¶ Parameters
¶ Standard tab
Parameters:
- Truncate the table at the first failing row
- Interpreter
- Expression
- Comment
¶ Others tab
Parameters:
- Add _nowS as input variable for dates saved as string
- Date format
- Time zone is UTC
- Add _now as input variable for dates saved as elapsed time
- Elapsed Time Unit
- Reference time
- Convert empty string to NULL Elapsed Time
- When converting from string to date in Elapsed Time, if time earlier than the reference
- When converting from string to daye in Elapsed Time, on format not recognized
- Add _n as input variable for raw numbers
- Seed for pseudo-random numbers (-1 for variable seed)
- Use JIT compiler
- In case of error while executinh the pipeline
¶ About
The first output pin contains all rows that match the given condition. The second output pin contains the rows that do not match.
For example, to select all people with an age greater than 25, we would use:
The expressions given inside FilterRows action follows the same syntax as the expressions from the Calculator Action.
One very common usage of the FilterRows action is to create a sample. Samples are extremely useful for reducing computation time during development. Typically, we develop new data transformation pipelines on small samples, and once they work correctly, we run the pipeline on the full dataset.
For example, if we want to select the first 100 rows of the input table:
(The variable _n represents the row number.)
Please note that we have checked the option "Truncate table on first failed row." When this option is enabled, ETL will stop reading the input table as soon as it encounters the first row where the expression evaluates to false.
With this option active, both expressions—_n < 100 and (_n < 100) || (_n > 200)—will result in selecting only the first 100 rows of the input table.
The main purpose of this option is to provide a performance hint to ETL, helping reduce computing time. For example, when using the expression _n < 100, it is safe to enable this option since there is no need to read beyond the first 100 rows.
NOTE :
When the “Truncate table on first failed row” option is enabled the table on the second output pin of the FilterRows action is always empty.
NOTE :
When the expression is “_n< [number]”, then ETL automatically checks/enables for you the “Truncate table on first failed row” option (because 99% of the time, this is what you really want, even if you forgot to check it).This also means that, when the expression is “_n< [number]”, you’ll always obtain an empty table table on the second output pin of the FilterRows action. If you want a non-empty table on the second pin, re-write you expression in the following way: “[number]>_n”.
¶ list Of Available Functions
¶ On Numbers
| Function | Description |
|---|---|
| abs(x) | absolute value of x (x<0?-x:x) |
| floor(x) | returns the largest integral value that is not greater than x (round down) |
| floor2(x,y) | returns the largest multiple of y lower or equal to x |
| ceil(x) | returns the smallest integral value that is not less than x (round up) |
| round(x) | round to closest integer |
| round2(x,d) | rounds x to a specified number of digits d |
| roundUp(x,d) | rounds up x to a specified number of digits d |
| roundDown(x,d) | rounds down x to a specified number of digits d |
| rand(x) | uniform random number inside [0 x[ |
| randN(x) | uniform random integer number inside [0 x[ |
| rand1() | uniform random number inside [0 1[ |
| sin(x) | sine function |
| cos(x) | cosine function |
| tan(x) | tangens function |
| asin(x) | arcus sine |
| acos(x) | arcus cosine |
| atan(x) | arcus tangens |
| atan2(y,x) | return the angle between (1,0) and (x,y) |
| cosh(x) | hyperbolic sine |
| sinh(x) | hyperbolic cosine |
| tanh(x) | hyperbolic tangens |
| asinh(x) | hyperbolic arcus sine |
| acosh(x) | hyperbolic arcus cosine |
| atanh(x) | hyperbolic arcus tangens |
| ln(x) | Natural logarithm |
| log(x) | Logarithm base 10 |
| log10(x) | Logarithm base 10 |
| log2(x) | Logarithm base 2 |
| exp(x) | e to the power of x |
| sqr(x) | x² |
| sqrt(x) | square root of x |
| min(x,y,z,...) | Returns the minimum value from all of its function arguments. |
| max(x,y,z,...) | Returns the maximum value from all of its function arguments. |
| sum(x,y,z,...) | Returns the sum value from all of its function arguments. |
| nanvl(x,y) | if x is nan or NULL return y, otherwise return x |
| nanvl2(x,y,z) | if x is nan or NULL return z, otherwise return y |
| finitvel(x,y) | if x is finite return x, otherwise return y |
| finitvel2(x,y,z) | if x is finite return y, otherwise return z |
| isNan(x) | Returns true if x is nan |
| isFinite(x) | Returns true if x is finite (i.e. not nan, not infinite, not neg.infinite) |
¶ On Strings
| Function | Description |
|---|---|
| contains(x,y) | Is y contained in x? |
| indexOf(x,y) | returns the position of y inside x (returns -1 if not found). |
| indexOf2(x,y,p) | returns the position of y inside x starting at position p. |
| lastIndexOf(x,y) | returns the last position of y inside x (returns -1 if not found). |
| lastIndexOf2(x,y,p) | returns the last position of y inside x starting at position p. |
| countText(x,y) | count the number of occurrences of y inside x |
| substr(x,b,l) | extract from the string x a substring beginning at character of index 'b' and of length 'l' |
| mid(x,b,l) | same as above |
| substring(x,b,e) | extract from the string x a substring beginning at index 'b' and ending at index 'e' |
| replace(s,b,a) | Replaces every occurrence of the string <before> with the string <after> and returns result |
| left(s,l) | returns a substring composed of the first 'l' characters of string s |
| right(s,l) | returns a substring composed of the last 'l' characters of string s |
| startsWith(x,s) | return true if the string x starts with the string s |
| endsWith(x,s) | return true if the string x ends with the string s |
| strlen(s) | returns the length of the string s |
| toupper(s) | converts the string s to uppercase characters |
| tolower(s) | converts the string s to lowercase characters |
| toCaseFold(s) | converts the string s to CaseFolded characters |
| trim(s) | returns the string s trimmed |
| ltrim(s) | returns the string s left-trimmed |
| rtrim(s) | returns the string s right-trimmed |
| trim2(x,y) | returns the string x without y at the end/start |
| isNumber(x) | test if the x string contains a number |
| isNumberStr(x) | test if the x string is a number |
| isInteger(x) | test if the x string contains an integer number |
| isIntegerStr(x) | test if the x string is an integer number |
| codepoint(x) | returns the unicode codepoint of the first char of x |
¶ String Encode
| Function | Description |
|---|---|
| atof(s) | converts the string stored in s into a floating point value. |
| ftoa(x) | converts the floating point value stored in x into a string. |
| itoa(x) | convert the integer value stored in x into a string. |
| urlEncode(x) | encode the string x to include in a URL. |
| xmlEncode(x) | encode the string x to include in XML. |
| jsonEncode(x) | encode the string x to include in JSON. |
| atob64(x) | encode the UTF-8 string x to Base64. |
| atob642(x) | encode the Latin-1 string x to Base64. |
| b64toa(x) | decode a Base64 value to a UTF-8 string. |
| b64toa2(x) | decode a Base64 value to a Latin-1 string. |
| htof(x) | convert x (a hexadecimal string) to a number. |
| ftoh(x) | convert x (a positive integer) to a hexadecimal string. |
| htoa(x) | decode a hexadecimal binary string x to a Latin-1 string. |
¶ On Dates
| Function | Description |
|---|---|
| toET(date) | convert a date (as a string) to an Elapsed Time |
| toETF(date) | convert a date (as a string) to an Elapsed Time (as a Float) |
| toDate(ET) | convert a date (as an Elapsed Time) to a string |
| etAdd(ET, y, M, d) | add/subtract a duration (in year, month and days) to an Elapsed Time |
| dateAdd(date, y, M, d) | add/subtract a duration (in year, month and days) to a date |
| dateAddS(date, s) | add/subtract a duration (in seconds) to a date |
| datePrint(date, format) | reformat a date (as a string) |
| etDiffInMonth(ET1, ET2) | number of months between ET1 and ET2 |
| etDiffInYear(ET1, ET2) | number of years between ET1 and ET2 |
| etWeekNumber(ET) | the week number (1 to 53, 0 if error) |
| etDayOfWeek(ET) | the Day of Week (0 = monday; 6 = sunday) |
| etFirstDayOfYear(y, dow) | the ET of the first DayOfWeek(dow) of the year (y) |
| dateWeekNumber(date) | the week number (1 to 53, 0 if error) |
| dateDayOfWeek(date) | the Day of Week (0 = monday; 6 = sunday) |
| dateFirstDayOfYear(y, dow) | the date of the first DayOfWeek(dow) of the year (y) |
| dateDiffInMonth(date1, date2) | number of months between date1 and date2 |
| dateDiffInYear(date1, date2) | number of years between date1 and date2 |
| dateDiffInDay(date1, date2) | number of days between date1 and date2 |
| dateDiffInSecond(date1, date2) | number of seconds between date1 and date2 |
| dateTimeLT(x, y) | true if x is strictly before y (x < y) |
| dateTimeLE(x, y) | true if x is before y (x <= y) |
| dateLT(x, y) | true if date(x) is strictly before date(y) (date(x) < date(y)) |
| dateLE(x, y) | true if date(x) is before date(y) (date(x) <= date(y)) |
| dateEOM(date, x) | similar to "EOMONTH(date,x)" from Excel |
| dateEOQ(date, x) | similar to dateEOM() but for quarters |
| nDaysInMonth(month, year) | return the number of days in the given month |
¶ On IP's
| Function | Description |
|---|---|
| isip4(x) | test if x is a valid TCP-IP v4 address |
| ip4tof(x) | convert a tcp-ip v4 address to a number |
| ip4part(x, pos) | extract one of the 4 parts of a tcp-ip v4 address |
| ip4RangeLow(x) | convert the lower bound of a tcp-ip v4 address-range to a number |
| ip4RangeHigh(x) | convert the higher bound of a tcp-ip v4 address-range to a number |
¶ Operators
| Operator | Description |
|---|---|
| (a?b:c) | Ternary Operator: If a is true then return b, otherwise returns c |
| – | minus operator |
| (float) | convert to float (from boolean or integer) |
| (int) | convert to integer (from boolean or float) |
| +, –, *, /, ^ | Standard operators |
| % | modulo |
| && | Logical And |
| || | Logical Or |
| ==, !=, >, <, <=, >= | Logical Comparison |
| &, |, <<, >> | Bit manipulation (bit-wise AND, bit-wise OR, shift left/right) |
| // | String concatenation |
¶ Predefined Constants
| Constant | Description |
|---|---|
| _pi | 3.141592653589793238462643 |
| _e | Eulerian number (2.718281828459045235360287) |
| _null | the NULL value (meta-type is “number”) – cannot be used inside any binary operator |
| _nullS | the NULL string (meta-type is “string”) – cannot be used inside any binary operator |
| _nan | Not-a-Number (meta-type is “number”) |
| _infinity | Infinity (meta-type is “number”) |
| _now | the current date-time in Elapsed-Time format (if enabled) |
| _nowS | the current date-time in String format (if enabled) |
| _anatellaID | identifier for the current version of Anatella |
| _currentDir | the directory of the current .anatella file |
| _tempDir | the current temp directory |
| _currentFile | the current .anatella filepath |
| _uniqueID | an ID that is guaranteed to be unique |
¶ Postfix Operators
| Operator | Description |
|---|---|
| nano (1e-9) | |
| micro (1e-6) | |
| milli (1e-3) | |
| kilo (1e3) | |
| giga (1e6) | |
| mega (1e9) |
¶ Special
| Function | Description |
|---|---|
| not(x) | boolean not |
| isTrue(x) | return 1 if x is TRUE (1,Y,y,T,t,O,o,P,p), otherwise return 0 |
| isNull(x) | return 1 if x is NULL, otherwise return 0 |
| nvl(x, y) | return y if x is null, otherwise return x |
| nvl2(x, y, z) | return z if x is null, otherwise return y |
| load(filename) | load the number contained in the file |
| nDaysInMonth(month, year) | return the number of days in the given month |
| hdist(lat1, lon1, lat2, lon2) | return the earth distance using Haversine Formula (all coords. in decimal) |
| hdist2(lat1Lon1, lat2Lon2) | return the earth distance using Haversine Formula |
