TSV String, Sum Numeric Column<\/div>
Sums the values in the numeric column of a TSV. If the numeric field contains commas, they will be removed and then parsed. If you need to add up the values for each key, use Summary tabulation or Cross tabulation.<\/div><\/div>\n\n\n
<\/span>Configs<\/div>
\n
- \n
- <\/i><\/span> A1: Set TSV *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> B1: Set Column ID of Numeric Field (eg “3” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- C1: Select NUMERIC DATA that stores Total (update)<\/li>\n
- C2: Select NUMERIC DATA that stores Count (update)<\/li>\n
- C3: Select NUMERIC DATA that stores Average (update)<\/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\n\/\/\/\/\/\/\/\/ START "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\nmain();\nfunction main(){ \n\n\/\/\/\/ == Config Retrieving \/ \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 ==\nconst strTsv = configs.get( "StrConfA1" ); \/\/\/ REQUIRED \/\/\/\/\/\/\/\/\/\/\/\/\/\n if( strTsv === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {A1: Tsv} is empty \\n" );\n }\n const arr2dTsv = parseAsRectangular( strTsv );\nconst strNumField = configs.get( "StrConfB1" ); \/\/\/ REQUIRED \/\/\/\/\/\/\/\/\/\/\/\/\/\n if( strNumField === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B1: NumField} is empty \\n" );\n }\n const numNumField = parseInt( strNumField, 10 );\n if( isNaN( numNumField ) || numNumField < 0 ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B1: NumField} must be a positive integer \\n" );\n }\n if( numNumField >= arr2dTsv[0].length ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B1: NumField} is larger than TsvWidth \\n" );\n }\nconst numPocketTotal = configs.getObject( "SelectConfC1" ); \/\/ NotRequired \/\/\/\/\/\/\/\nconst numPocketCount = configs.getObject( "SelectConfC2" ); \/\/ NotRequired \/\/\/\/\/\/\/\nconst numPocketAve = configs.getObject( "SelectConfC3" ); \/\/ NotRequired \/\/\/\/\/\/\/\n\n\n\/\/\/\/ == Data Retrieving \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u306e\u53c2\u7167 ==\n\/\/ (Nothing. Retrieved via Expression Language in Config Retrieving)\n\n\n\/\/\/\/ == Calculating \/ \u6f14\u7b97 ==\n\n\/\/ sum up\nlet numTotal = 0;\nlet numCount = 0;\nfor( let i = 0; i < arr2dTsv.length; i++ ){\n let numValue = parseFloat( arr2dTsv[i][numNumField].replace(\/,\/g, '') );\n if( isNaN(numValue) ){\n engine.log( " AutomatedTask StringWarning:" +\n " CellStr is not numeric at line: " + i );\n numValue = 0;\n }else{\n numCount = numCount + 1;\n }\n numTotal += numValue;\n}\n\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nif( numPocketTotal !== null ){\n engine.setData( numPocketTotal, new java.math.BigDecimal( numTotal ) );\n}\nif( numPocketCount !== null ){\n engine.setData( numPocketCount, new java.math.BigDecimal( numCount ) );\n}\nif( numPocketAve !== null ){\n engine.setData( numPocketAve, new java.math.BigDecimal( numTotal \/ numCount ) );\n}\n\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n\n\/\/ Parses TSV string as two-dimensional rectangular data matrix and creates a 2D array.\nfunction parseAsRectangular( strTsv ){\n const arrTsv = strTsv.split("\\n");\n\n \/\/\/ Get numMinWidth and numMaxWidth (blank lines are excluded)\n let numMinWidth = Infinity; \/\/ cf. String-Type Max: 1 million\n let numMaxWidth = 0;\n let numBlanklines = 0;\n for( let i = 0; i < arrTsv.length; i++ ){\n if( arrTsv[i] === "" ){ \/\/ Skip blank lines\n numBlanklines += 1;\n continue;\n }\n let arrCells = arrTsv[i].split("\\t");\n if( numMinWidth > arrCells.length ){ numMinWidth = arrCells.length; }\n if( numMaxWidth < arrCells.length ){ numMaxWidth = arrCells.length; }\n }\n engine.log( " AutomatedTask TsvDataCheck:" + \n " MinWidth:" + numMinWidth +\n " MaxWidth:" + numMaxWidth +\n " Lines:" + arrTsv.length +\n " (BlankLines:" + numBlanklines + ")" );\n\n \/\/\/ Get numMinWidth and numMaxWidth (blank lines are excluded)\n let arr2dTsv = [];\n for( let i = 0; i < arrTsv.length; i++ ){\n if( arrTsv[i] === "" ){ \/\/ Skip blank lines\n continue;\n }\n let arrTmp = [];\n let arrCells = arrTsv[i].split("\\t");\n for( let j = 0; j < numMaxWidth; j++ ){\n if( j < arrCells.length ){\n arrTmp[j] = arrCells[j];\n }else{\n arrTmp[j] = "";\n }\n }\n arr2dTsv.push( arrTmp );\n }\n\n return arr2dTsv;\n}\n\n\/*\nNotes:\n- When the process reaches this automated task, TSV is automatically read.\n - TSV: Monthly sales, monthly access log, etc.\n- If there is a blank line in the input TSV text, it will be skipped.\n - The line feed code for the last line is not added either.\n\nAPPENDIX:\n- TSV (Tab Separated Values) text assumes rectangular data.\n - Data that is not rectangular is automatically formatted with empty characters.\n - Blank lines (including the end) are ignored.\n- Specify the field column with the column ID (starting with zero).\n - Parsing numeric field values depends on JavaScript `parseFloat()`.\n - If commas in the numeric field, they are considered a thousands separator (The removed string is parsed).\n - A string that cannot be parsed is considered zero.\n\n\nNotes-ja:\n- \u6848\u4ef6\u304c\u81ea\u52d5\u51e6\u7406\u5de5\u7a0b\u306b\u5230\u9054\u3057\u305f\u969b\u3001\u6587\u5b57\u5217\u578b\u30c7\u30fc\u30bf\u306b\u4fdd\u5b58\u3055\u308c\u3066\u3044\u308bTSV\u304c\u81ea\u52d5\u7684\u306b\u8aad\u307f\u8fbc\u307e\u308c\u307e\u3059\u3002\n - TSV: \u6708\u6b21\u58f2\u4e0a\u3001\u6708\u6b21\u30a2\u30af\u30bb\u30b9\u30ed\u30b0\u3001\u306a\u3069\n- \u5165\u529bTSV\u30c6\u30ad\u30b9\u30c8\u306b\u7a7a\u884c\u304c\u3042\u308b\u5834\u5408\u3001\u30b9\u30ad\u30c3\u30d7\u3055\u308c\u307e\u3059\u3002\n - \u51fa\u529bTSV\u306e\u6700\u7d42\u884c\u306b\u6539\u884c\u30b3\u30fc\u30c9\u306f\u4ed8\u4e0e\u3055\u308c\u307e\u305b\u3093\u3002\n\nAPPENDIX-ja:\n- TSV\uff08Tab Separated Values\uff09\u30c6\u30ad\u30b9\u30c8\u306f\u3001\u77e9\u5f62\u30c7\u30fc\u30bf\u3092\u524d\u63d0\u3068\u3057\u307e\u3059\u3002\n - \u77e9\u5f62\u3067\u306a\u3044\u30c7\u30fc\u30bf\u306f\u3001\u7a7a\u6587\u5b57\u306b\u3088\u3063\u3066\u81ea\u52d5\u6574\u5f62\u3055\u308c\u307e\u3059\u3002\n - \u7a7a\u884c\uff08\u672b\u5c3e\u6539\u884c\u3092\u542b\u3080\uff09\u306f\u7121\u8996\u3055\u308c\u307e\u3059\u3002\n- \u30d5\u30a3\u30fc\u30eb\u30c9\u5217\u306e\u6307\u5b9a\u306f\u3001\u5217ID\uff08\u30bc\u30ed\u59cb\u307e\u308a\uff09\u3067\u8a2d\u5b9a\u3057\u3066\u304f\u3060\u3055\u3044\u3002\n - \u6570\u5024\u30d5\u30a3\u30fc\u30eb\u30c9\u5024\u306e\u89e3\u6790\uff08\u30d1\u30fc\u30b9\uff09\u306f JavaScript `parseFloat()` \u306b\u4f9d\u5b58\u3057\u307e\u3059\u3002\n - \u6570\u5024\u30d5\u30a3\u30fc\u30eb\u30c9\u5024\u306b\u30ab\u30f3\u30de\u304c\u5b58\u5728\u3059\u308b\u5834\u5408\u3001\u6841\u533a\u5207\u308a\u6587\u5b57\u3068\u307f\u306a\u3055\u308c\u307e\u3059\uff08\u9664\u53bb\u3055\u308c\u305f\u6587\u5b57\u5217\u304c\u89e3\u6790\u3055\u308c\u307e\u3059\uff09\u3002\n - \u89e3\u6790\u3067\u304d\u306a\u3044\u6587\u5b57\u5217\u306f\u30bc\u30ed\u3068\u898b\u306a\u3055\u308c\u307e\u3059\u3002\n*\/\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-sum-numeric-column-2021.xml<\/a><\/li>
- tsv-string-sum-numeric-column-202111.xml<\/a>
- “Sum of positive values” and “Sum of negative values” can also be calculated (enhanced)<\/li><\/ul><\/li><\/ul>\n\n\n\n
2021-11-19 (C) Questetra, Inc. (MIT License)
https:\/\/support.questetra.com\/addons\/tsv-string-sum-numeric-column-2021\/<\/a>
<\/i> The Add-on import feature is available with Professional<\/strong><\/span> edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.<\/p>\n\n\n<\/div>\n\n\n\n<\/i> Notes<\/h3>\n\n\n\n
- When the process reaches this automated task TSVs stores in String-type data are automatically read.
- TSV: Monthly sales, monthly access log, etc.<\/li><\/ul><\/li>
- If there is a blank line in the input TSV text it will be skipped.
- The line feed code for the last line is not added either.<\/li><\/ul><\/li><\/ul>\n\n\n\n
<\/i> Capture<\/h3>\n\n\n\n
![\"Sums](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/11\/tsv-string-sum-numeric-column-2021-capture-en.png?resize=1200%2C675&ssl=1\")
<\/a><\/figure>\n\n\n\n![\"Sums](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/11\/tsv-string-sum-numeric-column-2021-config-en-823x630.png?resize=823%2C630&ssl=1\")
<\/figure>\n\n\n\n<\/i> Appendix<\/h3>\n\n\n\n
- TSV (Tab Separated Values) text assumes rectangular data.
- Data that is not rectangular is automatically formatted with empty characters.<\/li>
- Blank lines (including trailing new lines) are ignored.<\/li><\/ul><\/li>
- Specify the field column with the column ID (starting with zero).
- Parsing numeric field values depends on JavaScript
parseFloat()<\/code>.<\/li>- If there are commas in the numeric field, they will be considered a thousands separator (the removed string is parsed).<\/li>
- A string that cannot be parsed is considered zero.<\/li><\/ul><\/li><\/ul>\n\n\n\n
<\/i> See also<\/h3>\n\n\n\n
- Parsing numeric field values depends on JavaScript
- TSV (Tab Separated Values) text assumes rectangular data.
- The line feed code for the last line is not added either.<\/li><\/ul><\/li><\/ul>\n\n\n\n
- When the process reaches this automated task TSVs stores in String-type data are automatically read.
- tsv-string-sum-numeric-column-202111.xml<\/a>
- <\/i><\/span> B1: Set Column ID of Numeric Field (eg “3” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n