Two Tsv Strings, Compare Numeric Cells

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, it will be compared with the key above.

2020-01-30 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/two-tsv-strings-compare-numeric-cells/

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
// (c) 2020, Questetra, Inc. (the MIT License)
// 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

Capture

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, it will be compared with the key above.

Notes

  1. Search for lines with the Key and compare the numeric cells.
  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}…

See also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: