TSV String, Filter by Datetime

TSV String, Filter by Datetime
TSV String, Filter by Datetime

Filters TSV strings using a Datetime range. Only the rows where the specified Datetime column is included in the Datetime range are output. If OR multiple ranges, like “2020-01-01 00:00<2020-01-07 23:59,2020-01-21 00:00<2020-01-27 23:59”.

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

Configs
  • A: Select STRING DATA for TSV String *
  • B-target: Set Numeric Column ID for Filtering (eg “0” ) * #{EL}
  • B-range: Set TimeFilter (eg “2010-01-01 00:00<2010-01-31 23:59”) * #{EL}
  • C: Select STRING DATA for Filterd TSV (update) *
Script
// (c) 2019, Questetra, Inc. (the MIT License)
// Notes:
// Supported datetime format is "YYYY-MM-DD HH:MM".
// 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 00:00<2019-10-05 00:00")
//
// Notes(ja):
// 対応する日時フォーマットは "YYYY-MM-DD HH:MM" です
// "AND検索" は自動工程の直列配置をお試しください
// 範囲境界値におけるイコールはフィルタ範囲に含まれます
// (問題のないフィルタ例 "2019-10-05 00:00<2019-10-05 00:00")

//////// 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 regBpmsYMDHM = /^\d{4}-\d{2}-\d{2} \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( ! regBpmsYMDHM.test( arrRangeMinMax[0] ) ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config DateRangeFr Format Error \n" );
  }
  if( ! regBpmsYMDHM.test( arrRangeMinMax[1] ) ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config DateRangeTo Format Error \n" );
  }
  let arrDatetimeFr  = arrRangeMinMax[0].split(" ");
  let arrDatePartsFr = arrDatetimeFr[0].split("-");
  let arrTimePartsFr = arrDatetimeFr[1].split(":");
  let dateFrom = new Date( arrDatePartsFr[0], arrDatePartsFr[1] - 1, arrDatePartsFr[2],
                                arrTimePartsFr[0], arrTimePartsFr[1]);
  let arrDatetimeTo  = arrRangeMinMax[1].split(" ");
  let arrDatePartsTo = arrDatetimeTo[0].split("-");
  let arrTimePartsTo = arrDatetimeTo[1].split(":");
  let dateTo   = new Date( arrDatePartsTo[0], arrDatePartsTo[1] - 1, arrDatePartsTo[2],
                                arrTimePartsTo[0], arrTimePartsTo[1]);
  // "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( ! regBpmsYMDHM.test( arrTsvLine[ numTargetColId ] ) ){ continue; }
  let arrTargetValue  = arrTsvLine[ numTargetColId ].split(" ");
  let arrTargetDateParts  = arrTargetValue[0].split("-");
  let arrTargetTimeParts  = arrTargetValue[1].split(":");
  let dateTargetValue = new Date(
                            arrTargetDateParts[0], 
                            arrTargetDateParts[1] - 1, 
                            arrTargetDateParts[2],
                            arrTargetTimeParts[0],
                            arrTargetTimeParts[1] );
  // "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 Datetime range. Only the rows where the specified Datetime column is included in the Datetime range are output. If OR multiple ranges, like "2020-01-01 00:00<2020-01-07 23:59,2020-01-21 00:00<2020-01-27 23:59".

Notes

  1. Supported datetime format is “YYYY-MM-DD HH:MM”.
  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 00:00<2019-10-05 00:00”)

See also

Leave a Reply

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

%d bloggers like this: