TSV String, Filter by Numeric
TSV String, Filter by Numeric

Filters TSV strings using a numeric range. Only the rows where the specified numeric column is included in the numeric range are output. If you want to OR multiple ranges, specify them in comma separated form like “-100<36.4,37.5<100”.

(C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/tsv-string-filter-by-numeric/

Configs
  • A: Select STRING DATA for TSV String *
  • B-target: Set Numeric Column ID for Filtering (eg “0” ) * #{EL}
  • B-range: Set Numeric Filter (eg “0<100” “-100<36.4,37.5<100”) * #{EL}
  • C: Select STRING DATA for Filterd TSV (update) *
Script (click to open)

// (c) 2019, Questetra, Inc. (the MIT License)
// Notes:
// 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 ",".
// If infinity, specify "(-)Infinity". (eg. "-Infinity<36.4,37.5<Infinity")
// For "AND search", try to place this Automated Step serially.
// Equals at range boundary values are included. (No Problem "37.0<37.0")
//
// Notes(ja):
// 数値判定は JavaScript parseFloat() に依存します
// 数値の前に "接頭辞" があると数値として判定されません ( 例 "$100" )
// ピリオド "." は小数点として認識されます
// 桁区切り文字を想定し、全てのカンマ "," を除去したうえで数値判定します
// 無限大は "(-)Infinity" を指定します (例 "-Infinity<36.4,37.5<Infinity")
// "AND検索" は自動工程の直列配置をお試しください
// 範囲境界値はフィルタ範囲に含まれます (問題のない設定例 "37.0<37.0")

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

//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA      = configs.get( "conf_DataIdA" ) + "";       // config required
const targetColId  = configs.get( "conf_TargetColId" ) + "";   // config required
const rangeFilters = configs.get( "conf_RangeFilters" ) + "";  // config required
const dataIdC      = configs.get( "conf_DataIdC" ) + "";       // config required
engine.log( " AutomatedTask Config: Target Column ID: " + targetColId );
engine.log( " AutomatedTask Config: Range Filters: " + rangeFilters );

if( targetColId === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Column ID} is empty \n" );
}
const numTargetColId = parseInt( targetColId );

if( rangeFilters === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Range Fileters} is empty \n" );
}
const arrRangeFilters = rangeFilters.split(",");


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


//// == Calculating / 演算 ==
// parse Filters
let arrFilterFromTo = [];
for( let i = 0; i < arrRangeFilters.length; i++ ){
  let arrRangeMinMax = arrRangeFilters[i].split("<");
  if( arrRangeMinMax.length !== 2 ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " {Range Fileter} must have one '<': " + 
                     arrRangeFilters[i] + " \n" );
  }
  let numRangeMin = parseFloat( arrRangeMinMax[0] );
  let numRangeMax = parseFloat( arrRangeMinMax[1] );
  if( numRangeMin > numRangeMax ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " {Range Fileter} must be Ascending Order: " + 
                     arrRangeFilters[i] + " \n" );
  }
  arrFilterFromTo.push( [ numRangeMin, numRangeMax] );
}

// Target matches Filter or not
let outputString = "";
for( let i = 0; i < arrInputString.length; i++ ){
  let arrTsvLine     = arrInputString[i].split("\t");
  let numTargetValue = parseFloat( arrTsvLine[ numTargetColId ].replace( /,/g,"") );
  if( isNaN(numTargetValue) ){ continue; }
  for( let j = 0; j < arrFilterFromTo.length; j++ ){
    if( arrFilterFromTo[j][0] <= numTargetValue &&
        numTargetValue <= arrFilterFromTo[j][1] ){
      outputString += arrInputString[i];
      outputString += "\n";
      break;
    }
  }
}
outputString = outputString.replace(/[\n]*$/, "");


//// == Data Updating / ワークフローデータへの代入 ==
if( outputString === "" ){
  engine.log( " AutomatedTask DataUpdating: Output Text empty" );
}
engine.setDataByNumber( dataIdC, outputString );

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

Download

    Capture

    Filters TSV strings using a numeric range. Only the rows where the specified numeric column is included in the numeric range are output. If you want to OR multiple ranges, specify them in comma separated form like "-100<36.4,37.5<100".

    Notes

    1. Numeric parsing depends on JavaScript parseFloat().
    2. If a “prefix” precedes a number, it will not be determined as a number. (eg “$ 100”)
    3. The period “.” is recognized as a decimal point.
    4. Assumes a digit separator and evaluates all numbers after removing all commas “,”.
    5. If infinity, specify “(-)Infinity”. (eg. “-Infinity<36.4,37.5<Infinity”)
    6. For “AND search”, try to place this Automated Step serially.
    7. Equals at range boundary values are included. (No Problem “37.0<37.0”)

    See also

    2 thoughts on “TSV String, Filter by Numeric”

    1. Pingback: Two Tsv Strings, Compare Numeric Cells – Questetra Support

    2. Pingback: TSV Filtering by Number – Questetra Support

    Leave a Reply

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

    %d