// GraalJS Script (engine type: 2)
// Notes:
// Search for lines with the Key and compare the numeric cells.
// Assumes a digit separator and evaluates all numbers after removing all commas ",".
// {Growth Value} and {Growth Rate} are calculated.
// "Infinity" is output for the zero denominator.
// Value Rate and the values of the specified B column IDs are output in TSV.
// {Growth Value} {Growth Rate} {B-Column0} {B-Column1}...
//
// Notes(ja):
// 同一Keyの行を探索し、数値セル同士を比較します。
// 桁区切り文字を想定し、全てのカンマ “,” を除去したうえで数値判定します
// {増加値}と{増加率}が算出されます。
// ゼロ分母は "Infinity" が出力されます。
// {増加値} {増加率} および指定したBカラムIDの値がTSV出力されます。
// {増加値} {増加率} {B-Column0} {B-Column1}...
//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const strA = configs.get( "conf_StrA" ) + ""; // config required
const idKeyA = configs.get( "conf_IdKeyA" ) + ""; // config required
const idNumA = configs.get( "conf_IdNumA" ) + ""; // config required
const strB = configs.get( "conf_StrB" ) + ""; // config required
const idKeyB = configs.get( "conf_IdKeyB" ) + ""; // config required
const idNumB = configs.get( "conf_IdNumB" ) + ""; // config required
const dataIdC = configs.get( "conf_DataIdC" ) + ""; // config required
const colIds = configs.get( "conf_ColIds" ) + ""; // config not required
if( strA === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {Str A} is empty \n" );
}
if( strB === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {Str B} is empty \n" );
}
if( idKeyA === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {KeyID A} is empty \n" );
}
if( idKeyB === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {KeyID B} is empty \n" );
}
if( idNumA === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {NumID A} is empty \n" );
}
if( idNumB === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {NumID B} is empty \n" );
}
const zeroPositiveInt = /^([1-9]\d*|0)$/; // RegExp
let numIdKeyA = -1;
if( zeroPositiveInt.test( idKeyA ) ){
numIdKeyA = parseInt(idKeyA, 10);
}else{
throw new Error( "\n AutomatedTask ConfigError:" +
" Config Number {KeyID A} must be a positive integer or zero \n" );
}
let numIdKeyB = -1;
if( zeroPositiveInt.test( idKeyB ) ){
numIdKeyB = parseInt(idKeyB, 10);
}else{
throw new Error( "\n AutomatedTask ConfigError:" +
" Config Number {KeyID B} must be a positive integer or zero \n" );
}
let numIdNumA = -1;
if( zeroPositiveInt.test( idNumA ) ){
numIdNumA = parseInt(idNumA, 10);
}else{
throw new Error( "\n AutomatedTask ConfigError:" +
" Config Number {NumID A} must be a positive integer or zero \n" );
}
let numIdNumB = -1;
if( zeroPositiveInt.test( idNumB ) ){
numIdNumB = parseInt(idNumB, 10);
}else{
throw new Error( "\n AutomatedTask ConfigError:" +
" Config Number {NumID B} must be a positive integer or zero \n" );
}
const idsCsv = /^[0-9][0-9,]*$/; // RegExp
if( ! idsCsv.test( colIds ) ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {Column IDs} allows only numbers and commas. \n" );
}
const arrColIds = colIds.split(",");
//// == Data Retrieving / ワークフローデータの参照 ==
// Nothing (except EL-expression Config)
//// == Calculating / 演算 ==
let twoDimArrayA = []; // for TSV-A
const arrStrA = strA.split("\n");
for( let i = 0; i < arrStrA.length; i++ ){
twoDimArrayA[i] = arrStrA[i].split("\t");
}
let twoDimArrayB = []; // for TSV-B
const arrStrB = strB.split("\n");
for( let i = 0; i < arrStrB.length; i++ ){
twoDimArrayB[i] = arrStrB[i].split("\t");
}
engine.log( " AutomatedTask MultilineString:" +
" TSV {A}, number of lines " + arrStrA.length );
engine.log( " AutomatedTask MultilineString:" +
" TSV {B}, number of lines " + arrStrB.length );
let strOutput = "";
for( let i = 0; i < arrStrB.length; i++ ){
for( let j = 0; j < arrStrA.length; j++ ){
if( twoDimArrayB[i][numIdKeyB] === twoDimArrayA[j][numIdKeyA] ){
engine.log( " AutomatedTask Match:" +
" B[" + i + "][" + numIdKeyB + "] ==" +
" A[" + j + "][" + numIdKeyA + "]" );
strOutput += (
parseFloat( twoDimArrayB[i][numIdNumB].replace( /,/g,"") ) -
parseFloat( twoDimArrayA[j][numIdNumA].replace( /,/g,"") )
) + "\t" +
Math.floor(
(
parseFloat( twoDimArrayB[i][numIdNumB].replace( /,/g,"") ) /
parseFloat( twoDimArrayA[j][numIdNumA].replace( /,/g,"") )
) * 1000
) / 1000 + "";
for( let k = 0; k < arrColIds.length; k++ ){
strOutput += "\t" + twoDimArrayB[i][ (arrColIds[k] - 0) ];
}
strOutput += "\n";
break;
}
}
}
strOutput = strOutput.replace(/[\n]*$/, "");
//// == Data Updating / ワークフローデータへの代入 ==
if( strOutput === "" ){
engine.log( " AutomatedTask DataUpdating: Output Text empty" );
}
engine.setDataByNumber( dataIdC, strOutput );
} //////// END "main()" /////////////////////////////////////////////////////////////////
Pingback: String, Batch Add Thousands Separators – Questetra Support