Row expression

Unfold Hierarchy number Tags Values
      Row expression
        1  Example
            1.1  Row calculation
                1.1.1  Numbers
                    1.1.1.1  Data row number
Row expression
#{DataRowNumber}
                    1.1.1.2  Table row number
Row expression
#{TableRowNumber}
                    1.1.1.3  Math calculation
Row expression
Math.abs(#{numberColumn1}) + Math.PI * Math.sqrt(#{numberColumn2})
                    1.1.1.4  Calculation including statistic
Row expression
#{numberColumn1} - #{numberColumn2-Mean}}
                1.1.2  String
                    1.1.2.1  Length
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.length
else
    -1
                    1.1.2.2  Lowercase
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.toLowerCase()
else
    null
                    1.1.2.3  Split
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.split(',')
else
    null
                    1.1.2.4  contain sub-string
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.search(/hello/ig)
else
    -1
                    1.1.2.5  Position of sub-string
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.indexOf('hello')
else
    -1
                    1.1.2.6  replace first
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.replace("hello", "Hello");
else
   null
                    1.1.2.7  replace all
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.replace(/hello/g, "Hello");
else
   null
                    1.1.2.8  Replace in case-insensitive
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.replace(/hello/ig,'Hello')
else
    null
                    1.1.2.9  Special values
Row expression
var array = [ 'A', 'B', 'C', 'D' ];
array.includes('#{stringColumn}')
                1.1.3  Date
                    1.1.3.1  Format
Row expression
function formatDate(date) {
     var y = date.getFullYear();
     var m = date.getMonth() + 1;
     m = m < 10 ? ('0' + m) : m;
     var d = date.getDate();
     d = d < 10 ? ('0' + d) : d;
     var h =date.getHours();
     h = h < 10 ? ('0' + h) : h;
     var M =date.getMinutes();
     M = M < 10 ? ('0' + M) : M;
     var s =date.getSeconds();
     s = s < 10 ? ('0' + s) : s;
     return y + '-' + m + '-' + d + ' ' + h + ':' + M + ':' + s;
}
if (#{dateColumn} != null)
   formatDate(new Date(#{dateColumn}));
else
   null;
                    1.1.3.2  Year
Row expression
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getFullYear()
else
    null
                    1.1.3.3  Month
Row expression
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getMonth() + 1
else
    null
                    1.1.3.4  Date
Row expression
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getDate()
else
    null
                    1.1.3.5  Hors
Row expression
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getHours()
else
    null
                    1.1.3.6  Minutes
Row expression
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getMinutes()
else
    null
                    1.1.3.7  Second
Row expression
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getSeconds()
else
    null
                    1.1.3.8  Day
Row expression
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getDay()
else
    null
            1.2  Row Filter
                1.2.1  Number
                    1.2.1.1  Is not number
Row expression
isNaN(#{stringColumn} - 1)
                    1.2.1.2  Compare
Row expression
#{numberColumn1} != 0 || #{numberColumn1} < #{numberColumn2}
                    1.2.1.3  Compare with statistic
Row expression
#{numberColumn} < #{numberColumn-Mean}
                1.2.2  Date
                    1.2.2.1  value of date
Row expression
if (#{dateColumn} != null)
   new Date(#{dateColumn}.getTime()
else
   -1;
                    1.2.2.2  Special time
Row expression
#{dateColumn} == '2016-05-19 11:34:28'
                    1.2.2.3  Match time
Row expression
if (#{dateColumn} != null)
   #{dateColumn}.startsWith('2016-05-19 09')
else
   undefined;
                    1.2.2.4  Compare time
Row expression
if (#{dateColumn} != null)
   new Date(#{dateColumn}).getTime()  > new Date('2016/05/19 09:23:12').getTime()
else
   undefined;
                    1.2.2.5  Format Date
Row expression
function formatDate(date) {
     var y = date.getFullYear();
     var m = date.getMonth() + 1;
     m = m < 10 ? ('0' + m) : m;
     var d = date.getDate();
     d = d < 10 ? ('0' + d) : d;
     var h =date.getHours();
     h = h < 10 ? ('0' + h) : h;
     var M =date.getMinutes();
     M = M < 10 ? ('0' + M) : M;
     var s =date.getSeconds();
     s = s < 10 ? ('0' + s) : s;
     return y + '-' + m + '-' + d + ' ' + h + ':' + M + ':' + s;
}
if (#{dateColumn} != null)
   formatDate(new Date(#{dateColumn}));
else
   null;
                    1.2.2.6  whether Sunday
Row expression
if (#{dateColumn} != null)
   new Date(#{dateColumn}).getDay() == 0
else
   undefined;
                1.2.3  String
                    1.2.3.1  string is empty
Row expression
#{stringColumn} == ''
                    1.2.3.2  string is not null
Row expression
#{stringColumn} != null
                    1.2.3.3  compare strings
Row expression
#{stringColumn} === 'hello'
                    1.2.3.4  starts with
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.startsWith('Hello')
else
   undefined
                    1.2.3.5  ends with
Row expression
if ( #{stringColumn} != null ) 
    #{stringColumn}.endsWith('hello')
else
   undefined