TSV String, Filter by Datetime<\/div>
Filters TSV strings using a Datetime range. Only the rows where the specified Datetime column is included in the Datetime range are output. If you want to OR multiple ranges, use comma-separated values like “2021-01-01 00:00<2021-01-07 23:59,2021-01-21 00:00<2021-01-27 23:59”.<\/div><\/div>\n\n\n
<\/span>Configs<\/div>
\n
- \n
- <\/i><\/span> A: Select STRING DATA for TSV String *<\/span><\/li>\n
- <\/i><\/span> B-target: Set Numeric Column ID for Filtering (eg “0” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> B-range: Set TimeFilter (eg “2021-05-01 00:00<2021-05-31 23:59”) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C: Select STRING DATA for Filterd TSV (update) *<\/span><\/li>\n<\/ul>\n<\/div><\/div>\n\n\n
<\/span>Script (click to open)<\/div>\n\n\n\n\/\/ GraalJS Script (engine type: 2)\n\/\/ (c) 2021, Questetra, Inc. (the MIT License)\n\/\/ Notes:\n\/\/ Supported datetime format is "YYYY-MM-DD HH:MM".\n\/\/ For "AND search", try to place this Automated Step serially.\n\/\/ Equals at range boundary values are included in the filter range.\n\/\/ (No Problem "2021-10-05 00:00<2021-10-05 00:00")\n\/\/\n\/\/ Notes(ja):\n\/\/ \u5bfe\u5fdc\u3059\u308b\u65e5\u6642\u30d5\u30a9\u30fc\u30de\u30c3\u30c8\u306f "YYYY-MM-DD HH:MM" \u3067\u3059\n\/\/ "AND\u691c\u7d22" \u306f\u81ea\u52d5\u5de5\u7a0b\u306e\u76f4\u5217\u914d\u7f6e\u3092\u304a\u8a66\u3057\u304f\u3060\u3055\u3044\n\/\/ \u7bc4\u56f2\u5883\u754c\u5024\u306b\u304a\u3051\u308b\u30a4\u30b3\u30fc\u30eb\u306f\u30d5\u30a3\u30eb\u30bf\u7bc4\u56f2\u306b\u542b\u307e\u308c\u307e\u3059\n\/\/ \uff08\u554f\u984c\u306e\u306a\u3044\u30d5\u30a3\u30eb\u30bf\u4f8b "2021-10-05 00:00<2021-10-05 00:00"\uff09\n\n\/\/\/\/\/\/\/\/ START "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\nmain();\nfunction main(){ \n\n\/\/\/\/ == Config Retrieving \/ \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 ==\nconst dataIdA = configs.get( "conf_DataIdA" ) + ""; \/\/ config required\nconst targetColId = configs.get( "conf_TargetColId" ) + ""; \/\/ config required\nconst rangeFilters = configs.get( "conf_RangeFilters" ) + ""; \/\/ config required\nconst dataIdC = configs.get( "conf_DataIdC" ) + ""; \/\/ config required\nengine.log( " AutomatedTask Config: Target Column ID: " + targetColId );\nengine.log( " AutomatedTask Config: Range Filters: " + rangeFilters );\n\nif( targetColId === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {Column ID} is empty \\n" );\n}\nconst numTargetColId = parseInt( targetColId );\n\nif( rangeFilters === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {Range Fileters} is empty \\n" );\n}\nconst arrRangeFilters = rangeFilters.split(",");\n\n\n\/\/\/\/ == Data Retrieving \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u306e\u53c2\u7167 ==\nconst inputString = engine.findDataByNumber( dataIdA ) + "";\nconst arrInputString = inputString.split("\\n");\nengine.log( " AutomatedTask MultilineString:" + \n " TSV {A}, number of lines: " + arrInputString.length );\n\n\n\/\/\/\/ == Calculating \/ \u6f14\u7b97 ==\n\/\/ BPMS Date Format\nconst regBpmsYMDHM = \/^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}$\/;\n\n\/\/ parse Filters\nlet arrFilterFromTo = [];\nfor( let i = 0; i < arrRangeFilters.length; i++ ){\n let arrRangeMinMax = arrRangeFilters[i].split("<");\n if( arrRangeMinMax.length !== 2 ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " {Range Fileter} must have one '<': " + \n arrRangeFilters[i] + " \\n" );\n }\n if( ! regBpmsYMDHM.test( arrRangeMinMax[0] ) ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config DateRangeFr Format Error \\n" );\n }\n if( ! regBpmsYMDHM.test( arrRangeMinMax[1] ) ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config DateRangeTo Format Error \\n" );\n }\n let arrDatetimeFr = arrRangeMinMax[0].split(" ");\n let arrDatePartsFr = arrDatetimeFr[0].split("-");\n let arrTimePartsFr = arrDatetimeFr[1].split(":");\n let dateFrom = new Date( arrDatePartsFr[0], arrDatePartsFr[1] - 1, arrDatePartsFr[2],\n arrTimePartsFr[0], arrTimePartsFr[1]);\n let arrDatetimeTo = arrRangeMinMax[1].split(" ");\n let arrDatePartsTo = arrDatetimeTo[0].split("-");\n let arrTimePartsTo = arrDatetimeTo[1].split(":");\n let dateTo = new Date( arrDatePartsTo[0], arrDatePartsTo[1] - 1, arrDatePartsTo[2],\n arrTimePartsTo[0], arrTimePartsTo[1]);\n \/\/ "Note: TimeZoneOffset has been added"\n\n if( dateFrom > dateTo ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " {Range Fileter} must be Ascending Order: " + \n arrRangeFilters[i] + " \\n" );\n }\n arrFilterFromTo.push( [ dateFrom, dateTo] );\n}\n\n\/\/ Target matches Filter or not\nlet outputString = "";\nfor( let i = 0; i < arrInputString.length; i++ ){\n let arrTsvLine = arrInputString[i].split("\\t");\n if( ! regBpmsYMDHM.test( arrTsvLine[ numTargetColId ] ) ){ continue; }\n let arrTargetValue = arrTsvLine[ numTargetColId ].split(" ");\n let arrTargetDateParts = arrTargetValue[0].split("-");\n let arrTargetTimeParts = arrTargetValue[1].split(":");\n let dateTargetValue = new Date(\n arrTargetDateParts[0], \n arrTargetDateParts[1] - 1, \n arrTargetDateParts[2],\n arrTargetTimeParts[0],\n arrTargetTimeParts[1] );\n \/\/ "Note: TimeZoneOffset has been added"\n\n for( let j = 0; j < arrFilterFromTo.length; j++ ){\n if( arrFilterFromTo[j][0] <= dateTargetValue &&\n dateTargetValue <= arrFilterFromTo[j][1] ){\n outputString += arrInputString[i];\n outputString += "\\n";\n break;\n }\n }\n}\noutputString = outputString.replace(\/[\\n]*$\/, "");\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nif( outputString === "" ){\n engine.log( " AutomatedTask DataUpdating: Output Text empty" );\n}\nengine.setDataByNumber( dataIdC, outputString );\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n<\/code><\/pre><\/div>\n\n\n<\/div><\/div>\n\n\n\n - <\/i><\/span> B-target: Set Numeric Column ID for Filtering (eg “0” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
![\"\"](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/07\/tsv-string-filter-by-datetime-2021-en.png?ssl=1\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Filter-by-Datetime-capture-en-1024x576.png?resize=1024%2C576&ssl=1\")
<\/figure>\n\n\n\n