TSV String, Create Cross Tab for Numerical Sum

TSV String, Create Cross Tab for Numerical Sum
TSV String, Create Cross Tab for Numerical Sum

Creates a crosstab (pivot table) for numeric fields in TSV data. For example, the “Amount” in the order history (TSV) is aggregated on two axes, “Responsible” and “Product”. The fields for X-axis and Y-axis aggregation are specified by TSV field ID.

2019-12-05 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-create-cross-tab-for-numerical-sum/

Configs
  • A: Select STRING DATA for TSV String *
  • B-target: Set Col-IDs for Sum, X-flds and Y-flds (eg “3,0,2”) * #{EL}
  • B-order: Set DESC or ASC (default “DESC”) #{EL}
  • C: Select STRING DATA for newly generated Crosstab TSV (update) *
Script
// (c) 2019, Questetra, Inc. (the MIT License)
// Notes:
// A timeout may occur depending on the number of records or aggregate labels.
// Aggregate labels are sorted in ascending or descending order.
// Numeric parsing depends on JavaScript parseFloat().
// If a "prefix" precedes a number, it will not be determined as a number. (eg "$ 100")
// The period "." is recognized as a decimal point.
// Assumes a digit separator and evaluates all numbers after removing all commas ",".

// Notes(ja):
// 履歴TSVのレコード数(行数)や集約ラベルの数によってはタイムアウトとなります。
// 集約ラベルは昇順もしくは降順でソートされます。
// 数値判定は JavaScript parseFloat() に依存します
// 数値の前に "接頭辞" があると数値として判定されません ( 例 "$100" )
// ピリオド "." は小数点として認識されます
// 桁区切り文字を想定し、全てのカンマ "," を除去したうえで数値判定します


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

//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA      = configs.get( "conf_DataIdA" ) + "";      // config required
const targetColIds = configs.get( "conf_TargetColIds" ) + ""; // config required
let   sortOrder    = configs.get( "conf_SortOrder" ) + "";    // config not required
const dataIdC      = configs.get( "conf_DataIdC" ) + "";      // config required
engine.log( " AutomatedTask Config: Crosstab Target Columns: " + targetColIds );
engine.log( " AutomatedTask Config: Sort Order: " + sortOrder );

const arrTargetColIds = targetColIds.split(",");
if( arrTargetColIds.length !== 3 ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Target Col-IDs} length must be three \n" );
}
const intCalcTarget = parseInt( arrTargetColIds[0] );
const intXFieldCol  = parseInt( arrTargetColIds[1] );
const intYFieldCol  = parseInt( arrTargetColIds[2] );

if( sortOrder !== "ASC" ){
  sortOrder = "DESC";
}


//// == Data Retrieving / ワークフローデータの参照 ==
const inputString = engine.findDataByNumber( dataIdA ) + "";
let   arrInputString = inputString.split("\n");
engine.log( " AutomatedTask MultilineString:" + 
            " TSV {A}, number of lines: " + arrInputString.length );


//// == Calculating / 演算 ==
// Extract X-flds Y-flds
let arrXFields = [];
let arrYFields = [];
for( let i = 0; i < arrInputString.length; i++ ){
  let arrTsvLine = arrInputString[i].split("\t");
  if( arrXFields.indexOf( arrTsvLine[ intXFieldCol ] ) == -1){
      arrXFields.push(    arrTsvLine[ intXFieldCol ] );
  }
  if( arrYFields.indexOf( arrTsvLine[ intYFieldCol ] ) == -1){
      arrYFields.push(    arrTsvLine[ intYFieldCol ] );
  }
}
arrXFields.sort( function( strA, strB ){
  if( sortOrder === "DESC" ){ // from Z to A
    if( strA < strB ){ return 1;}
    if( strA > strB ){ return -1;}
  }else{ // ASC: alphabetical from A to Z
    if( strA > strB ){ return 1;}
    if( strA < strB ){ return -1;}
  }
});
arrYFields.sort( function( strA, strB ){
  if( sortOrder === "DESC" ){ // from Z to A
    if( strA < strB ){ return 1;}
    if( strA > strB ){ return -1;}
    return 0;
  }else{ // ASC: alphabetical from A to Z
    if( strA > strB ){ return 1;}
    if( strA < strB ){ return -1;}
    return 0;
  }
});

// Calculate outputTable[y][x]
let grandTotal = 0;
let outputTable = new Array( arrYFields.length );
for( let i = 0; i < arrYFields.length; i++ ){
  outputTable[i] = new Array( arrXFields.length );
  for( let j = 0; j < arrXFields.length; j++ ){
    outputTable[i][j] = 0; // initialize
  }
}
for( let i = 0; i < arrInputString.length; i++ ){
  let arrTsvLine = arrInputString[i].split("\t");
  outputTable[
    arrYFields.indexOf( arrTsvLine[ intYFieldCol ] )
  ][
    arrXFields.indexOf( arrTsvLine[ intXFieldCol ] )
  ] += parseFloat( arrTsvLine[ intCalcTarget ].replace( /,/g,"") );
  grandTotal += parseFloat( arrTsvLine[ intCalcTarget ].replace( /,/g,"") );
}

// Convert to TSV String
let outputTsv = "Crosstab\t";
for( let i = 0; i < arrXFields.length; i++ ){
  outputTsv += arrXFields[i] + "\t";
}
outputTsv += "total\n"; // header ends
for( let i = 0; i < outputTable.length; i++ ){
  outputTsv += arrYFields[i] + "\t";
  for( let j = 0; j < outputTable[i].length; j++ ){
    outputTsv += outputTable[i][j] + "\t";
  }
  outputTsv += sum( outputTable[i] ) + "\n";
}
outputTsv += "total\t";
for( let i = 0; i < arrXFields.length; i++ ){
  let vsum = 0;
  for( let j = 0; j < arrYFields.length; j++ ){
    vsum += outputTable[j][i];
  }
  outputTsv += vsum + "\t";
}
outputTsv += grandTotal + "";


//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdC, outputTsv );

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


function sum(arrF){
  let tmp = 0;
  for( let i = 0; i < arrF.length; i++){ tmp += arrF[i]; }
  return tmp;
}

Download

Capture

Creates a crosstab (pivot table) for numeric fields in TSV data. For example, the "Amount" in the order history (TSV) is aggregated on two axes, "Responsible" and "Product". The fields for X-axis and Y-axis aggregation are specified by TSV field ID.

Notes

  1. A timeout may occur depending on the number of records or aggregate labels.
  2. Aggregate labels are sorted in ascending or descending order.
  3. Numeric parsing depends on JavaScript parseFloat().
  4. If a “prefix” precedes a number, it will not be determined as a number. (eg “$ 100”)
  5. The period “.” is recognized as a decimal point.
  6. Assumes a digit separator and evaluates all numbers after removing all commas “,”.
  7. (2019-12-06) If non-numeric data is mixed in the specified column, the record is regarded as zero and not added.

See also

Leave a Reply

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

%d bloggers like this: