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.
| Objective | Regular expression | Replacement string | Examples of Values | Non null Groups | Formula after replacement string | Result after EvalEx computation |
|---|---|---|---|---|---|---|
| Remove the character "$" before Pivot calculation | (\$)(.*) | $2 | $ 25 | $0 = $ 25 | 25 | 25 |
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 | (3*8 + 5) | 29 |
| Remove the character "h" at the end of the string | (.*)(h) | $1 | 60.45h | $0 = 60.45h | 60.45 | 60.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 | 123456.78 | 123456.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 used 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 | ROUND((01 * 5 * 5) + (01 * 5) + 01 + 0/60 + 0/3600, 0) | 31 | ||
| 12.51w 13.65d, 14.78h, 18.54m, 30.90s | ROUND((012.51 * 5 * 5) + (013.65 * 5) + 014.78 + 018.54/60 + 030.90/3600,0) | 396 |