TSV String, Create Summary Table for Numeric Column
TSV String, Create Summary Table for Numeric Column
Aggregates the values in a numeric column by aggregation-keys. Sum, Percentage, Count and Average are tabulated as a summary table TSV. For example, “Sales for each client” is aggregated from “Sales log TSV”. Grand Total Tabulation.
Configs
  • A1: Set TSV *#{EL}
  • B1: Set Column ID of Numeric Field (eg “3” ) *#{EL}
  • B2: Set Column ID of Aggregation-Key Field (eg “4” ) *#{EL}
  • C1: To Sort by Sum Value, Set DECS or ASC (eg “ASC” )#{EL}
  • C2: To Sort by Agg-Key, Set DECS or ASC (eg “ASC” )#{EL}
  • D1: Select STRING DATA that stores Summary TSV (update) *
  • D2: Select NUMERIC DATA that stores Grand Total (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 strYaggField   = configs.get( "StrConfB2" );           /// REQUIRED /////////////
  if( strYaggField === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B2: Y-AggField} is empty \n" );
  }
  const numYaggField = parseInt( strYaggField, 10 );
  if( isNaN( numYaggField ) || numYaggField < 0 ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B2: YaggField} must be a positive integer \n" );
  }
  if( numYaggField  >= arr2dTsv[0].length ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B2: YaggField} is larger than TsvWidth \n" );
  }
const strNumSort     = configs.get( "StrConfC1" );           // NotRequired ///////////
const strYaggSort    = configs.get( "StrConfC2" );           // NotRequired ///////////

const strPocketOutput     = configs.getObject( "SelectConfD1" ); // NotRequired ///////
const numPocketGrandTotal = configs.getObject( "SelectConfD2" ); // NotRequired ///////


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


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

// omit repeated Keys in Y-Aggregation Field values (make Keys uniq)
let arrYaggKeys = [];
for( let i = 0; i < arr2dTsv.length; i++ ){
  if( arrYaggKeys.indexOf( arr2dTsv[i][ numYaggField ] ) === -1){
      arrYaggKeys.push(    arr2dTsv[i][ numYaggField ] );
  }
  // Array.indexOf(): strict equality `===` 
  // https://developer.mozilla.org/docs/Web/JavaScript/Reference/Global_Objects/Array/indexOf
}

// sort by Keys
if( strYaggSort === "ASC" ){ // ASC: ascending alphabetical from A to Z
  arrYaggKeys.sort( function( strA, strB ){
    if( strA > strB ){ return  1; }
    if( strA < strB ){ return -1; }
    return 0;
  });
}
if( strYaggSort === "DESC" ){ // DESC: descending from Z to A
  arrYaggKeys.sort( function( strA, strB ){
    if( strA < strB ){ return  1; }
    if( strA > strB ){ return -1; }
    return 0;
  });
}

// initialize arr2dOutput[y][x]
let arr2dOutput = [];
for( let i = 0; i < arrYaggKeys.length; i++ ){
  arr2dOutput[i] = [];
  arr2dOutput[i][0] = arrYaggKeys[i];
  arr2dOutput[i][1] = 0; // sum
  arr2dOutput[i][2] = 0; // share
  arr2dOutput[i][3] = 0; // count
  arr2dOutput[i][4] = 0; // ave
} // arr2dOutput.length === arrYaggKeys.length

// sum up
let numGrandTotal = 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;
  }
  let strKey = arr2dTsv[i][numYaggField];
  arr2dOutput[ arrYaggKeys.indexOf( strKey ) ][1] += numValue;
  arr2dOutput[ arrYaggKeys.indexOf( strKey ) ][3] += 1;
  numGrandTotal += numValue;
}

for( let i = 0; i < arr2dOutput.length; i++ ){
  arr2dOutput[i][2] = (arr2dOutput[i][1] / numGrandTotal).toFixed(3);
  arr2dOutput[i][4] = (arr2dOutput[i][1] / arr2dOutput[i][3]).toFixed(3);
}

// sort by Total
if( strNumSort === "ASC" ){ // ASC: ascending alphabetical from 0 to 10
  arr2dOutput.sort( function( arrA, arrB ){
    if( arrA[1] > arrB[1] ){ return  1; }
    if( arrA[1] < arrB[1] ){ return -1; }
    return 0;
  });
}
if( strNumSort === "DESC" ){ // DESC: descending from 10 to 0
  arr2dOutput.sort( function( arrA, arrB ){
    if( arrA[1] < arrB[1] ){ return  1; }
    if( arrA[1] > arrB[1] ){ return -1; }
    return 0;
  });
}

// output Summary Tabulation
let strOutput = "";
for( let i = 0; i < arr2dOutput.length; i++ ){
  strOutput += arr2dOutput[i].join("\t") + "\n";
}
strOutput = strOutput.slice( 0, -1 ); // delete last "\n"


//// == Data Updating / ワークフローデータへの代入 ==
engine.setData( strPocketOutput, strOutput );
if( numPocketGrandTotal !== null ){
  engine.setData( numPocketGrandTotal, new java.math.BigDecimal( numGrandTotal ) );
}


} //////// 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.
- The output TSV text (total table TSV, etc.) is rectangular data.
    - The number of rows in TSV is the number of types of "aggregate Key".
    - The number of columns of TSV is 5 of "key", "sum", "percentage", "count", and "average".

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.
- Only "DESC" or "ASC" is valid for the sort config.
    - If no sort setting, it will be in the order of appearance.
    - Character sorting is in code order.
    - If both character sort and numeric sort are configured, numeric sort is performed after character sort.
- 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の最終行に改行コードは付与されません。
- 出力されるTSVテキスト(合計値テーブルTSV等)は、矩形データです。
    - TSVの行数は "集約Key" の種類数になります。
    - TSVの列数は、"集約Key列" "合算値列" "割合値列" "個数列" "平均値列" の5です。

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

Download

2021-09-06 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-create-summary-table-for-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 stored in String-type data will be loaded automatically.
    • 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.
      - The output TSV text (total table TSV, etc.) is rectangular data.
    • The number of rows in the TSV will be the number of “Aggregate Key” types.
    • The number of columns in the TSV is 5: key, sum, percentage, count, and average.

Capture

Aggregates the values in a numeric column by aggregation-keys. Sum, Percentage, Count and Average are tabulated as a summary table TSV. For example, "Sales for each client" is aggregated from "Sales log TSV". Grand Total Tabulation.
Aggregates the values in a numeric column by aggregation-keys. Sum, Percentage, Count and Average are tabulated as a summary table TSV. For example, "Sales for each client" is aggregated from "Sales log TSV". Grand Total Tabulation.

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.
  • Only “DESC” or “ASC” is valid for the sort config.
    • If there is no sort setting, it will be ordered by appearance.
    • Character sorting is in code order.
    • If both character sort and numeric sort are configured, numeric sort is performed after character sort.
  • Specify the field column with the column ID (starting with zero).
    • Parsing numeric field values depends on JavaScript parseFloat().
    • If commas are present in the numeric field, they are considered a thousands separator (The removed string will be 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: