TSV String, Calc Changes in Two Numeric Columns
Calculates the difference and rate of change for two numeric columns in a TSV. Adds the calculated value to the rightmost column. It realizes month-on-month and year-on-year automation.
Configs
  • A1: Set TSV *#{EL}
  • B1: Set Column ID of Base Field (eg “3” ) *#{EL}
  • B2: Set Column ID of Target Field (eg “4” ) *#{EL}
  • C1: Select STRING that stores TSV with Calced Columns (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 strBaseId    = configs.get( "StrConfB1" );          /// REQUIRED //////////////
  if( strBaseId  === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B1: BaseId} is empty \n" );
  }
  const numBaseId  = parseInt( strBaseId, 10 );
  if( isNaN( numBaseId ) || numBaseId < 0 ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B1: BaseId} must be a positive integer \n" );
  }
  if( numBaseId   >= arr2dTsv[0].length ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B1: BaseId} is larger than TsvWidth \n" );
  }
const strTargetId    = configs.get( "StrConfB2" );          /// REQUIRED //////////////
  if( strTargetId  === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B2: TargetId} is empty \n" );
  }
  const numTargetId  = parseInt( strTargetId, 10 );
  if( isNaN( numTargetId ) || numTargetId < 0 ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B2: TargetId} must be a positive integer \n" );
  }
  if( numTargetId   >= arr2dTsv[0].length ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B2: TargetId} is larger than TsvWidth \n" );
  }
const strPocketOutput  = configs.getObject( "SelectConfC1" ); /// REQUIRED //////////////


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


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

// compare Target field with Base field
let strOutput = "";
for( let i = 0; i < arr2dTsv.length; i++ ){
  let numTarget = parseFloat( arr2dTsv[i][numTargetId].replace(/,/g, '') );
  if( isNaN(numTarget) ){ numTarget = 0; }
  let numBase   = parseFloat( arr2dTsv[i][numBaseId].replace(/,/g, '')   );
  if( isNaN(numBase) ){ numBase = 0; }

  let numDiff = numTarget - numBase;
  let strRate = (numTarget / numBase).toFixed(3);
  strOutput  += arr2dTsv[i].join("\t") + "\t";
  strOutput  += numDiff + "\t";
  strOutput  += strRate;
  strOutput += "\n";
}
strOutput = strOutput.slice( 0, -1 ); // delete last "\n"


//// == Data Updating / ワークフローデータへの代入 ==
engine.setData( strPocketOutput, strOutput );


} //////// 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 total, monthly access total, etc.
- 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.
- Two columns will be added on the right. (Difference / Rate)
    - Diff: {B2: Target field} - {B1: Base field}
    - Rate: {B2: Target field} / {B1: Base field}
        - The number of digits to appear after the decimal point is 3.
- Set the fields with the column ID (starting with zero).
    - String that cannot be parsed by JavaScript `parseFloat()` are considered zero.
- To delete unnecessary column, place "TSV String, Convert" etc. downstream of the workflow.
    - https://support.questetra.com/addons/tsv-string-convert-2021/

APPENDIX:
- 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 number of cells in output TSV (combined TSV) is uniform for each row.
    - If the number of cells in TsvA is not uniform in each row, blank strings will be added.
- Division by zero is output as `Infinity` or `NaN`.


Notes-ja:
- 案件が自動処理工程に到達した際、文字列型データに保存されているTSVが自動的に読み込まれます。
    - TSV: 月次売上集計、月次アクセス集計、など
- TSV(Tab Separated Values)テキストは、矩形データを前提とします。
    - 矩形でないデータは、空文字によって自動整形されます。
    - 空行(末尾改行を含む)は無視されます。
- 最も右側に2列が追加されます。(差分・変化率)
    - 差分: {B2:計測値フィールド} - {B1:基準値フィールド}
    - 変化率: {B2:計測値フィールド} / {B1:基準値フィールド}
        - 小数点以下の表示桁数は3桁です。
- フィールド列は、列ID(ゼロ始まり)で設定してください。
    - JavaScript `parseFloat()` で解析できない文字列はゼロと見なされます。
- 不要なデータ列は『TSV 文字列, 変換』等をワークフロー下流に配置して削除します。
    - https://support.questetra.com/ja/addons/tsv-string-convert-2021/

APPENDIX-ja:
- 入力TSVテキストに空行がある場合、スキップされます。
    - 最終行の改行コードも付与されません。
- 出力されるTSV(結合されたTSV)のセル数は各行で均一となります。
    - TsvAのセル数が各行で均一でなかった場合、空文字が追加されます。
- 0 の除算は `Infinity` や `NaN` と出力されます。
*/

Download

2021-08-30 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-calc-changes-in-two-numeric-columns-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, the TSV is automatically read.
    • TSV: Monthly sales total, monthly access total, etc.
  • 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.
  • Two columns will be added on the right. (Difference / Rate)
    • Diff: {B2: Target field} – {B1: Base field}
    • Rate: {B2: Target field} / {B1: Base field}
      • The number of digits to appear after the decimal point is 3.
  • Set the fields with the column ID (starting with zero).
    • Strings that cannot be parsed by JavaScript parseFloat() are considered to be zero.
  • To delete unnecessary columns, place “TSV String, Convert” etc. downstream in the workflow.

Capture

Calculates the difference and rate of change for the two numeric columns in the TSV. Adds the calculated value to the rightmost column. It realizes month-on-month and year-on-year automation.
Calculates the difference and rate of change for the two numeric columns in the TSV. Adds the calculated value to the rightmost column. It realizes month-on-month and year-on-year automation.

Appendix

  • 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 number of cells in the (merged) output TSV is uniform for each row.
    • If the number of cells in TsvA is not uniform in each row, blank strings will be added.
  • Division by zero is output as Infinity or NaN.

See also

1 thought on “TSV String, Calc Changes in Two Numeric Columns”

  1. Pingback: TSV String, Create Summary Table for Numeric Column – Questetra Support

Leave a Reply

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

%d bloggers like this: