TSV String, Filter by Date
TSV String, Filter by Date

Filters TSV strings using a date range. Only the rows where the specified date column is included in the date range are output. If OR multiple ranges, specify in comma separated like “2020-01-01<2020-01-07,2020-01-21<2020-01-27”.

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

Configs
  • A: Select STRING DATA for TSV String *
  • B-target: Set Date-info Column ID for Filtering (eg “0” ) * #{EL}
  • B-range: Set Date Filter (eg “2010-01-01<2010-01-31”) * #{EL}
  • C: Select STRING DATA for Filterd TSV (update) *
Script
// (c) 2019, Questetra, Inc. (the MIT License)
// Notes:
// Supported date format is "YYYY-MM-DD".
// For "AND search", try to place this Automated Step serially.
// Equals at range boundary values are included in the filter range.
//  (No Problem "2019-10-05<2019-10-05")
//
// Notes(ja):
// 対応する日付フォーマットは "YYYY-MM-DD" です
// "AND検索" は自動工程の直列配置をお試しください
// 範囲境界値におけるイコールはフィルタ範囲に含まれます
// (問題のないフィルタ例 "2019-10-05<2019-10-05")

//////// 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 / 演算 ==
// BPMS Date Format
const regBpmsYMD = /^\d{4}-\d{2}-\d{2}$/;

// 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" );
  }
  if( ! regBpmsYMD.test( arrRangeMinMax[0] ) ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config DateRangeFr Format Error \n" );
  }
  if( ! regBpmsYMD.test( arrRangeMinMax[1] ) ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config DateRangeTo Format Error \n" );
  }
  let arrDatePartsFr = arrRangeMinMax[0].split("-");
  let arrDatePartsTo = arrRangeMinMax[1].split("-");
  let dateFrom = new Date( arrDatePartsFr[0], arrDatePartsFr[1] - 1, arrDatePartsFr[2] );
  let dateTo   = new Date( arrDatePartsTo[0], arrDatePartsTo[1] - 1, arrDatePartsTo[2] );
  // "Note: TimeZoneOffset has been added"

  if( dateFrom > dateTo ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " {Range Fileter} must be Ascending Order: " + 
                     arrRangeFilters[i] + " \n" );
  }
  arrFilterFromTo.push( [ dateFrom, dateTo] );
}

// Target matches Filter or not
let outputString = "";
for( let i = 0; i < arrInputString.length; i++ ){
  let arrTsvLine     = arrInputString[i].split("\t");
  if( ! regBpmsYMD.test( arrTsvLine[ numTargetColId ] ) ){ continue; }
  let arrTargetValue  = arrTsvLine[ numTargetColId ].split("-");
  let dateTargetValue = new Date( arrTargetValue[0], arrTargetValue[1] - 1, arrTargetValue[2] );
  // "Note: TimeZoneOffset has been added"

  for( let j = 0; j < arrFilterFromTo.length; j++ ){
    if( arrFilterFromTo[j][0] <= dateTargetValue && dateTargetValue <= 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 date range. Only the rows where the specified date column is included in the date range are output. If OR multiple ranges, specify in comma separated like "2020-01-01<2020-01-07,2020-01-21<2020-01-27".

Notes

  1. Supported date format is “YYYY-MM-DD”.
  2. For “AND search”, try to place this Automated Step serially.
  3. Equals at range boundary values are included in the filter range.
    1. (No Problem “2019-10-05<2019-10-05”)

See also

1 thought on “TSV String, Filter by Date”

  1. Pingback: TSV Filtering by Date – Questetra Support

Leave a Reply

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

%d bloggers like this: