Two Tsv Strings, Compare Numeric Cells
Two Tsv Strings, Compare Numeric Cells
Compares numeric column strings in TSV-A and TSV-B, and lists the amount of change from A to B. If there is no string that matches the key column of A, it will not be listed. If there are multiple matches, they will be compared to the numbers in the key rows above them.
Configs
  • A: Set TSV String *#{EL}
  • A-key-id: Set ID of key column *#{EL}
  • A-num-id: Set ID of numeric column *#{EL}
  • B: Set TSV String *#{EL}
  • B-key-id: Set ID of key column *#{EL}
  • B-num-id: Set ID of numeric column *#{EL}
  • C: Select STRING DATA for Extracted TSV String (update) *
  • D: Set Output Col-IDs following {Value-Rate} (eg “0,1,3,0”)#{EL}
Script (click to open)

// 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()" /////////////////////////////////////////////////////////////////

Download

2021-07-27 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/two-tsv-strings-compare-numeric-cells-2021/
The Add-on import feature is available with Professional or Enterprise edition.

Notes

  1. Searches for rows with the same Key and compares numeric cells with each other.
  2. Assumes a digit separator and evaluates all numbers after removing all commas “,”.
  3. {Growth Value} and {Growth Rate} are calculated.
    1. “Infinity” is output for the zero denominator.
  4. Value Rate and the values of the specified B column IDs are output in TSV.
    1. {Growth Value} {Growth Rate} {B-Column0} {B-Column1}…

Capture

See also

%d bloggers like this: