// (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;
}
Pingback: Email-HTML String, Create TsvTable – Questetra Support