Pivot Table Regular expressions and formula string replacement examples

On this page:

Overview

Since version 1.4.0, Pivot Table allows to add a regular expression and a string replacement on the values of the body table.

Regular expression follows Java pattern specification.

Replacement string follows also Java pattern specification, section "Groups and capturing". The replaced string can be a formula that must follow EvalEx syntax. EvalEx is a handy expression evaluator that allows to evaluate simple mathematical and boolean expressions.

Examples

To obtain the same result as described in column Result after EvalEx computation below, copy/paste the values defined in rows Regular Expression and Replacement string in the macro editor.

ObjectiveRegular expressionReplacement stringExamples of ValuesNon null GroupsFormula after replacement stringResult after EvalEx computation
Remove the character "$" before Pivot calculation(\$)(.*)$2$ 25

$0 = $ 25
$1 = $
$2 = 25

2525

Convert a string containing a number of days and a number of hours in a number of hours.

Assumption: 1 day = 8 hours.

(\d*)(d) (\d*)(h) ($1*8 + $3)3 d 5 h

$0 = 3d 5h
$1 = 3
$2 = d
$3 = 5
$4 = h

 (3*8 + 5)29
Remove the character "h" at the end of the string(.*)(h)$160.45h

$0 = 60.45h
$1 = 60.45
$2 = h

60.4560.45
Convert a number which uses german locale (ie period as thousands and comma as decimal separator) to french locale (ie no period as thousands, and period as decimal separator)

((\d*)(\.)){0,1}(\d*)((,)(\d*)){0,1}

$2$4.$7

123.456,78

$0 = 123.456,78
$1 = 123.456
$2 = 123
$3 = .
$4 = 456
$5 = ,78
$6 = ,
$7 = 78

123456.78123456.78
Convert the Original Estimate string provided by JIRA in a number of rounded days with one decimal

When the following regular expression is applied, the replacement string can use the extracted values by using the notation ${position} where :

$0 contains the number of weeks

$10 contains the number of days

$18 contains the number of hours

$26 contains the number of minutes

$34 contains the number of seconds

Regular expression:

(((\d*)([\.|,]){0,1}(\d*))( ){0,1}(w)){0,1}(\D*){0,1}(((\d*)([\.|,]){0,1}(\d*))( ){0,1}(d)){0,1}(\D*){0,1}(((\d*)([\.|,]){0,1}(\d*))( ){0,1}(h)){0,1}(\D*){0,1}(((\d*)([\.|,]){0,1}(\d*))( ){0,1}(m)){0,1}(\D*){0,1}(((\d*)([\.|,]){0,1}(\d*))( ){0,1}(s)){0,1}(\D*){0,1}

ROUND((0$2 * 5) + (0$10 ) + (0$18/5), 1)2 days, 3 hours
ROUND((00 * 5) + (02) + (03/5), 1)2.6
Convert the Original Estimate string provided by JIRA in a number of rounded hours.ROUND((0$2 * 5 * 5) + (0$10 * 5) + 0$18 + 0$26/60 + 0$34/3600,0)1 week, 1 day, 1 hour
 Click here to expand...

$0 = 1 week, 1 day, 1 hour
$1 = 1 w
$2 = 1
$3 = 1
$5 =
$6 =
$7 = w
$8 = eek,
$9 = 1 d
$10 = 1
$11 = 1
$13 =
$14 =
$15 = d
$16 = ay,
$17 = 1 h
$18 = 1
$19 = 1
$21 =
$22 =
$23 = h
$24 = our
$32 =
$40 =

ROUND((01 * 5 * 5) + (01 * 5) + 01 + 0/60 + 0/3600, 0)31
12.51w 13.65d, 14.78h, 18.54m, 30.90s
 Click here to expand...

$0 = 12.51w 13.65d, 14.78h, 18.54m, 30.90s
$1 = 12.51w
$2 = 12.51
$3 = 12
$4 = .
$5 = 51
$7 = w
$8 =
$9 = 13.65d
$10 = 13.65
$11 = 13
$12 = .
$13 = 65
$15 = d
$16 = ,
$17 = 14.78h
$18 = 14.78
$19 = 14
$20 = .
$21 = 78
$23 = h
$24 = ,
$25 = 18.54m
$26 = 18.54
$27 = 18
$28 = .
$29 = 54
$31 = m
$32 = ,
$33 = 30.90s
$34 = 30.90
$35 = 30
$36 = .
$37 = 90
$39 = s
$40 =

ROUND((012.51 * 5 * 5) + (013.65 * 5) + 014.78 + 018.54/60 + 030.90/3600,0)396