TSV String, Create Cross Tab for Numerical Sum
TSV String, Create Cross Tab for Numerical Sum
Creates a crosstab table (or pivot table) for the numeric fields of TSV data. For example, the “Amount” in the order history (TSV) is aggregated on the two axes of “Person Responsible” and “Product”. The fields for X-axis and Y-axis aggregation are specified by the TSV field ID.
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 (click to open)
// GraalJS Script (engine type: 2)
// (c) 2021, 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");
  let numValue = parseFloat( arrTsvLine[ intCalcTarget ].replace( /,/g,"") );
  if ( isNaN(numValue) ){
    engine.log( " AutomatedTask StringWarning:" +
                " CellStr is not numeric at line: " + i );
    numValue = 0;
  }
  outputTable[
    arrYFields.indexOf( arrTsvLine[ intYFieldCol ] )
  ][
    arrXFields.indexOf( arrTsvLine[ intXFieldCol ] )
  ] += numValue;
  grandTotal += numValue;
}

// 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

2021-07-16 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-create-cross-tab-for-numerical-sum-2021/
The Add-on import feature is available with Professional edition.

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 recognized as a number (e.g. “$100”).
  5. The period “.” is recognized as a decimal point.
  6. Assuming a digit separator, all commas will be removed before determining the numeric value.
  7. (2019-12-06) If non-numeric data is mixed in the specified column, the record is regarded as zero and not added.

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.

See also

3 thoughts on “TSV String, Create Cross Tab for Numerical Sum”

  1. Pingback: TSV String, Create Cross Tab for Numerical Sum – Questetra Support

  2. Pingback: Converter, CSV-String to TSV-String – Questetra Support

  3. Pingback: Multiline String, Delete Empty Lines – Questetra Support

Comments are closed.

%d