TSV String, Sum Numeric Column
TSV String, Sum Numeric Column
Sums the values in the numeric column of a TSV. If the numeric field contains commas, they will be removed and then parsed. If you need to add up the values for each key, use Summary tabulation or Cross tabulation.
Configs
  • A1: Set TSV *#{EL}
  • B1: Set Column ID of Numeric Field (eg “3” ) *#{EL}
  • C1: Select NUMERIC DATA that stores Total (update)
  • C2: Select NUMERIC DATA that stores Count (update)
  • C3: Select NUMERIC DATA that stores Average (update)
Script (click to open)
// GraalJS Script (engine type: 2)

//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strTsv         = configs.get( "StrConfA1" );           /// REQUIRED /////////////
  if( strTsv       === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: Tsv} is empty \n" );
  }
  const arr2dTsv     = parseAsRectangular( strTsv );
const strNumField    = configs.get( "StrConfB1" );           /// REQUIRED /////////////
  if( strNumField  === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B1: NumField} is empty \n" );
  }
  const numNumField  = parseInt( strNumField, 10 );
  if( isNaN( numNumField ) || numNumField < 0 ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B1: NumField} must be a positive integer \n" );
  }
  if( numNumField   >= arr2dTsv[0].length ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B1: NumField} is larger than TsvWidth \n" );
  }
const numPocketTotal = configs.getObject( "SelectConfC1" ); // NotRequired ///////
const numPocketCount = configs.getObject( "SelectConfC2" ); // NotRequired ///////
const numPocketAve   = configs.getObject( "SelectConfC3" ); // NotRequired ///////


//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)


//// == Calculating / 演算 ==

// sum up
let numTotal = 0;
let numCount = 0;
for( let i = 0; i < arr2dTsv.length; i++ ){
  let numValue = parseFloat( arr2dTsv[i][numNumField].replace(/,/g, '') );
  if( isNaN(numValue) ){
    engine.log( " AutomatedTask StringWarning:" +
                " CellStr is not numeric at line: " + i );
    numValue = 0;
  }else{
    numCount = numCount + 1;
  }
  numTotal += numValue;
}



//// == Data Updating / ワークフローデータへの代入 ==
if( numPocketTotal !== null ){
  engine.setData( numPocketTotal, new java.math.BigDecimal( numTotal ) );
}
if( numPocketCount !== null ){
  engine.setData( numPocketCount, new java.math.BigDecimal( numCount ) );
}
if( numPocketAve !== null ){
  engine.setData( numPocketAve, new java.math.BigDecimal( numTotal / numCount ) );
}


} //////// END "main()" /////////////////////////////////////////////////////////////////


// Parses TSV string as two-dimensional rectangular data matrix and creates a 2D array.
function parseAsRectangular( strTsv ){
  const arrTsv = strTsv.split("\n");

  /// Get numMinWidth and numMaxWidth (blank lines are excluded)
  let numMinWidth   = Infinity; // cf. String-Type Max: 1 million
  let numMaxWidth   = 0;
  let numBlanklines = 0;
  for( let i = 0; i < arrTsv.length; i++ ){
    if( arrTsv[i] === "" ){ // Skip blank lines
      numBlanklines += 1;
      continue;
    }
    let arrCells = arrTsv[i].split("\t");
    if( numMinWidth > arrCells.length ){ numMinWidth = arrCells.length; }
    if( numMaxWidth < arrCells.length ){ numMaxWidth = arrCells.length; }
  }
  engine.log( " AutomatedTask TsvDataCheck:" + 
              " MinWidth:" + numMinWidth +
              " MaxWidth:" + numMaxWidth +
              " Lines:" + arrTsv.length +
              " (BlankLines:" + numBlanklines + ")" );

  /// Get numMinWidth and numMaxWidth (blank lines are excluded)
  let arr2dTsv      = [];
  for( let i = 0; i < arrTsv.length; i++ ){
    if( arrTsv[i] === "" ){ // Skip blank lines
      continue;
    }
    let arrTmp = [];
    let arrCells = arrTsv[i].split("\t");
    for( let j = 0; j < numMaxWidth; j++ ){
      if( j < arrCells.length ){
        arrTmp[j] = arrCells[j];
      }else{
        arrTmp[j] = "";
      }
    }
    arr2dTsv.push( arrTmp );
  }

  return arr2dTsv;
}

/*
Notes:
- When the process reaches this automated task, TSV is automatically read.
    - TSV: Monthly sales, monthly access log, etc.
- If there is a blank line in the input TSV text, it will be skipped.
    - The line feed code for the last line is not added either.

APPENDIX:
- TSV (Tab Separated Values) text assumes rectangular data.
    - Data that is not rectangular is automatically formatted with empty characters.
    - Blank lines (including the end) are ignored.
- Specify the field column with the column ID (starting with zero).
    - Parsing numeric field values depends on JavaScript `parseFloat()`.
    - If commas in the numeric field, they are considered a thousands separator (The removed string is parsed).
    - A string that cannot be parsed is considered zero.


Notes-ja:
- 案件が自動処理工程に到達した際、文字列型データに保存されているTSVが自動的に読み込まれます。
    - TSV: 月次売上、月次アクセスログ、など
- 入力TSVテキストに空行がある場合、スキップされます。
    - 出力TSVの最終行に改行コードは付与されません。

APPENDIX-ja:
- TSV(Tab Separated Values)テキストは、矩形データを前提とします。
    - 矩形でないデータは、空文字によって自動整形されます。
    - 空行(末尾改行を含む)は無視されます。
- フィールド列の指定は、列ID(ゼロ始まり)で設定してください。
    - 数値フィールド値の解析(パース)は JavaScript `parseFloat()` に依存します。
    - 数値フィールド値にカンマが存在する場合、桁区切り文字とみなされます(除去された文字列が解析されます)。
    - 解析できない文字列はゼロと見なされます。
*/

Download

2021-11-19 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-sum-numeric-column-2021/
The Add-on import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

Notes

  • When the process reaches this automated task TSVs stores in String-type data are automatically read.
    • TSV: Monthly sales, monthly access log, etc.
  • If there is a blank line in the input TSV text it will be skipped.
    • The line feed code for the last line is not added either.

Capture

Sums the values in the numeric column in the TSV. If the numeric field contains commas, they will be removed and then parsed. If you need to add up for each key, use "Summary tabulation" or "Cross tabulation".
Sums the values in the numeric column in the TSV. If the numeric field contains commas, they will be removed and then parsed. If you need to add up for each key, use "Summary tabulation" or "Cross tabulation".

Appendix

  • TSV (Tab Separated Values) text assumes rectangular data.
    • Data that is not rectangular is automatically formatted with empty characters.
    • Blank lines (including trailing new lines) are ignored.
  • Specify the field column with the column ID (starting with zero).
    • Parsing numeric field values depends on JavaScript parseFloat().
    • If there are commas in the numeric field, they will be considered a thousands separator (the removed string is parsed).
    • A string that cannot be parsed is considered zero.

See also

Leave a Reply

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

%d bloggers like this: