TSV String, Sort by Numeric<\/div>
Sorts TSV text in the numerical order of the specified column. If each column values are “123 jpy”, “1,234 jpy”, and “12 jpy”, they are sorted in the order “line including 12 jpy”, “line including 123 jpy”, “line including 1,234 jpy”.<\/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-key: Set Column ID(s) for Sort Target (eg “0” “3,1” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> B-order: Set DESC or ASC ( default “DESC” )#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C: Select STRING DATA for Sorted 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\/\/ Numeric parsing depends on JavaScript parseFloat().\n\/\/ If a "prefix" precedes a number, it will not be determined as a number. (eg "$ 100")\n\/\/ The period "." is recognized as a decimal point.\n\/\/ Assumes a digit separator and evaluates all numbers after removing all commas ",".\n\/\/ If you want to specify multiple sort keys, use {Column ID} in CSV notation (eg "3,1,0")\n\/\/ Notes(ja):\n\/\/ \u6570\u5024\u5224\u5b9a\u306f JavaScript parseFloat() \u306b\u4f9d\u5b58\u3057\u307e\u3059\n\/\/ \u6570\u5024\u306e\u524d\u306b "\u63a5\u982d\u8f9e" \u304c\u3042\u308b\u3068\u6570\u5024\u3068\u3057\u3066\u5224\u5b9a\u3055\u308c\u307e\u305b\u3093 ( \u4f8b "$100" )\n\/\/ \u30d4\u30ea\u30aa\u30c9 "." \u306f\u5c0f\u6570\u70b9\u3068\u3057\u3066\u8a8d\u8b58\u3055\u308c\u307e\u3059\n\/\/ \u6841\u533a\u5207\u308a\u6587\u5b57\u3092\u60f3\u5b9a\u3057\u3001\u5168\u3066\u306e\u30ab\u30f3\u30de "," \u3092\u9664\u53bb\u3057\u305f\u3046\u3048\u3067\u6570\u5024\u5224\u5b9a\u3057\u307e\u3059\n\/\/ \u30bd\u30fc\u30c8\u30ad\u30fc\u3092\u8907\u6570\u6307\u5b9a\u3057\u305f\u3044\u5834\u5408\u3001{Column ID} \u3092CSV\u8868\u8a18\u3067\u884c\u3044\u307e\u3059 ( \u4f8b "3,1,0" )\n\n\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 targetColIds = configs.get( "conf_TargetColIds" ) + ""; \/\/ config required\nlet sortOrder = configs.get( "conf_SortOrder" ) + ""; \/\/ config not required\nconst dataIdC = configs.get( "conf_DataIdC" ) + ""; \/\/ config required\nengine.log( " AutomatedTask Config: Sort Key Column(s): " + targetColIds );\nengine.log( " AutomatedTask Config: Sort Order: " + sortOrder );\n\nif( targetColIds === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {Sort Column} is empty \\n" );\n}\nconst arrTargetColIds = targetColIds.split(",");\n\nif( sortOrder !== "ASC" ){\n sortOrder = "DESC";\n}\n\n\n\/\/\/\/ == Data Retrieving \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u306e\u53c2\u7167 ==\nconst inputString = engine.findDataByNumber( dataIdA ) + "";\nlet arrInputString = inputString.split("\\n");\nengine.log( " AutomatedTask MultilineString:" + \n " TSV {A}, number of lines: " + arrInputString.length );\n\n\n\/\/\/\/ == Calculating \/ \u6f14\u7b97 ==\narrInputString.sort( function( tsvlineA, tsvlineB ){\n let arrTsvlineA = tsvlineA.split("\\t");\n let arrTsvlineB = tsvlineB.split("\\t");\n\n if( sortOrder === "DESC" ){\n for( let i = 0; i < arrTargetColIds.length; i++ ){\n if( arrTsvlineA.length <= arrTargetColIds[i] ||\n arrTsvlineB.length <= arrTargetColIds[i] ){\n throw new Error( "\\n AutomatedTask UnexpectedNumberError:" +\n " Number {Sort Key ID} is larger than TSV length \\n" );\n }\n if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) <\n parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) ){ return 1; }\n if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) >\n parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) ){ return -1; }\n }\n }else{ \/\/ ASC: alphabetical from A to Z\n for( let i = 0; i < arrTargetColIds.length; i++ ){\n if( arrTsvlineA.length <= arrTargetColIds[i] ||\n arrTsvlineB.length <= arrTargetColIds[i] ){\n throw new Error( "\\n AutomatedTask UnexpectedNumberError:" +\n " Number {Sort Key ID} is larger than TSV length \\n" );\n }\n if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) >\n parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) ){ return 1; }\n if( parseFloat(arrTsvlineA[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) <\n parseFloat(arrTsvlineB[ parseInt(arrTargetColIds[i]) ].replace( \/,\/g,"")) ){ return -1; }\n }\n }\n return 0;\n});\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nengine.setDataByNumber( dataIdC, arrInputString.join("\\n") );\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n<\/code><\/pre><\/div>\n\n\n<\/div><\/div>\n\n\n\n<\/a><\/figure><\/div>\n\n\n\n
<\/i> Download<\/h3>\n\n\n\n
- Tsv-String-Sort-by-Numeric-2021.xml<\/a><\/li><\/ul>\n\n\n\n
2021-07-09 (C) Questetra, Inc. (MIT License)
https:\/\/support.questetra.com\/addons\/tsv-string-sort-by-numeric-2021\/<\/a>
<\/i> The Add-on import feature is available with Professional<\/strong><\/span> edition.<\/p>\n\n\n<\/div>\n\n\n\n<\/i> Notes<\/h3>\n\n\n\n
- Numeric parsing depends on JavaScript parseFloat()<\/a>.<\/li>
- If a prefix precedes a number, it will not be determined as a number. (e.g. “$100”)<\/li>
- The period “.” is recognized as a decimal point.<\/li>
- Assumes a digit separator and evaluates all numbers after removing all commas.<\/li>
- If you want to specify multiple sort keys, use {Column ID} in CSV notation (eg “3,1,0”)<\/li><\/ol>\n\n\n\n
<\/i> Capture<\/h3>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/07\/tsv-string-sort-by-numeric.png?ssl=1\")
<\/figure>\n\n\n\n![\"Sorts](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Sort-by-Numeric-capture-en.png?fit=1024%2C576&ssl=1\")
<\/a><\/figure>\n\n\n\n<\/i> See also<\/h3>\n\n\n\n
- Numeric parsing depends on JavaScript parseFloat()<\/a>.<\/li>
- <\/i><\/span> B-key: Set Column ID(s) for Sort Target (eg “0” “3,1” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n