TSV文字列, 数値合計のクロス集計
TSV文字列, 数値合計のクロス集計 (TSV String, Create Cross Tab for Numerical Sum)

TSVデータの数値フィールドについて、クロス集計表(ピボットテーブル)を作成します。たとえば受注履歴(TSV)の “金額” について “担当者” と “製品” の2軸で集計します。なお、X軸集約およびY軸集約で使用されるフィールドの指定には、TSVフィールドIDで設定します。

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

Configs
  • A: TSV文字列が格納されている文字列型データ項目を選択してください *
  • B-target: 合計・X軸集約・Y軸集約となるカラムのIDをセットしてください (例 “3,0,2”) * #{EL}
  • B-order: 降順(DESC)もしくは昇順(ASC)をセットしてください (デフォルト “DESC”) #{EL}
  • C: 新しく生成されるクロステーブルTSVが格納される文字列型データ項目を選択してください (更新) *
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

TSVデータの数値フィールドについて、クロス集計表(ピボットテーブル)を作成します。たとえば受注履歴(TSV)の "金額" について "担当者" と "製品" の2軸で集計します。なお、X軸集約およびY軸集約で使用されるフィールドの指定には、TSVフィールドIDで設定します。

Notes

  1. 履歴TSVのレコード数(行数)や集約ラベルの数によってはタイムアウトとなります。
  2. 集約ラベルは昇順もしくは降順でソートされます。
  3. 数値判定は JavaScript parseFloat() に依存します
  4. 数値の前に “接頭辞” があると数値として判定されません ( 例 “$100” )
  5. ピリオド “.” は小数点として認識されます
  6. 桁区切り文字を想定し、全てのカンマ “,” を除去したうえで数値判定します
  7. (2019-12-06) 指定カラムに数値以外のデータが混在している場合、当該レコードはゼロとみなされ和算されません

See also

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

%d人のブロガーが「いいね」をつけました。