TSV String, Create Summary Table for Numeric Column<\/div>
Aggregates the values in a numeric column by aggregation-keys. Sum, Percentage, Count and Average are tabulated as a summary table TSV. For example, “Sales for each client” is aggregated from “Sales log TSV”. Grand Total 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
- <\/i><\/span> B2: Set Column ID of Aggregation-Key Field (eg “4” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C1: To Sort by Sum Value, Set DECS or ASC (eg “ASC” )#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C2: To Sort by Agg-Key, Set DECS or ASC (eg “ASC” )#{EL}<\/sup><\/span><\/li>\n
- D1: Select STRING DATA that stores Summary TSV (update) *<\/span><\/li>\n
- D2: Select NUMERIC DATA that stores Grand Total (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 strYaggField = configs.get( "StrConfB2" ); \/\/\/ REQUIRED \/\/\/\/\/\/\/\/\/\/\/\/\/\n if( strYaggField === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B2: Y-AggField} is empty \\n" );\n }\n const numYaggField = parseInt( strYaggField, 10 );\n if( isNaN( numYaggField ) || numYaggField < 0 ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B2: YaggField} must be a positive integer \\n" );\n }\n if( numYaggField >= arr2dTsv[0].length ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B2: YaggField} is larger than TsvWidth \\n" );\n }\nconst strNumSort = configs.get( "StrConfC1" ); \/\/ NotRequired \/\/\/\/\/\/\/\/\/\/\/\nconst strYaggSort = configs.get( "StrConfC2" ); \/\/ NotRequired \/\/\/\/\/\/\/\/\/\/\/\n\nconst strPocketOutput = configs.getObject( "SelectConfD1" ); \/\/ NotRequired \/\/\/\/\/\/\/\nconst numPocketGrandTotal = configs.getObject( "SelectConfD2" ); \/\/ 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\/\/ omit repeated Keys in Y-Aggregation Field values (make Keys uniq)\nlet arrYaggKeys = [];\nfor( let i = 0; i < arr2dTsv.length; i++ ){\n if( arrYaggKeys.indexOf( arr2dTsv[i][ numYaggField ] ) === -1){\n arrYaggKeys.push( arr2dTsv[i][ numYaggField ] );\n }\n \/\/ Array.indexOf(): strict equality `===` \n \/\/ https:\/\/developer.mozilla.org\/docs\/Web\/JavaScript\/Reference\/Global_Objects\/Array\/indexOf\n}\n\n\/\/ sort by Keys\nif( strYaggSort === "ASC" ){ \/\/ ASC: ascending alphabetical from A to Z\n arrYaggKeys.sort( function( strA, strB ){\n if( strA > strB ){ return 1; }\n if( strA < strB ){ return -1; }\n return 0;\n });\n}\nif( strYaggSort === "DESC" ){ \/\/ DESC: descending from Z to A\n arrYaggKeys.sort( function( strA, strB ){\n if( strA < strB ){ return 1; }\n if( strA > strB ){ return -1; }\n return 0;\n });\n}\n\n\/\/ initialize arr2dOutput[y][x]\nlet arr2dOutput = [];\nfor( let i = 0; i < arrYaggKeys.length; i++ ){\n arr2dOutput[i] = [];\n arr2dOutput[i][0] = arrYaggKeys[i];\n arr2dOutput[i][1] = 0; \/\/ sum\n arr2dOutput[i][2] = 0; \/\/ share\n arr2dOutput[i][3] = 0; \/\/ count\n arr2dOutput[i][4] = 0; \/\/ ave\n} \/\/ arr2dOutput.length === arrYaggKeys.length\n\n\/\/ sum up\nlet numGrandTotal = 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 }\n let strKey = arr2dTsv[i][numYaggField];\n arr2dOutput[ arrYaggKeys.indexOf( strKey ) ][1] += numValue;\n arr2dOutput[ arrYaggKeys.indexOf( strKey ) ][3] += 1;\n numGrandTotal += numValue;\n}\n\nfor( let i = 0; i < arr2dOutput.length; i++ ){\n arr2dOutput[i][2] = (arr2dOutput[i][1] \/ numGrandTotal).toFixed(3);\n arr2dOutput[i][4] = (arr2dOutput[i][1] \/ arr2dOutput[i][3]).toFixed(3);\n}\n\n\/\/ sort by Total\nif( strNumSort === "ASC" ){ \/\/ ASC: ascending alphabetical from 0 to 10\n arr2dOutput.sort( function( arrA, arrB ){\n if( arrA[1] > arrB[1] ){ return 1; }\n if( arrA[1] < arrB[1] ){ return -1; }\n return 0;\n });\n}\nif( strNumSort === "DESC" ){ \/\/ DESC: descending from 10 to 0\n arr2dOutput.sort( function( arrA, arrB ){\n if( arrA[1] < arrB[1] ){ return 1; }\n if( arrA[1] > arrB[1] ){ return -1; }\n return 0;\n });\n}\n\n\/\/ output Summary Tabulation\nlet strOutput = "";\nfor( let i = 0; i < arr2dOutput.length; i++ ){\n strOutput += arr2dOutput[i].join("\\t") + "\\n";\n}\nstrOutput = strOutput.slice( 0, -1 ); \/\/ delete last "\\n"\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nengine.setData( strPocketOutput, strOutput );\nif( numPocketGrandTotal !== null ){\n engine.setData( numPocketGrandTotal, new java.math.BigDecimal( numGrandTotal ) );\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-\u3000The output TSV text (total table TSV, etc.) is rectangular data.\n - The number of rows in TSV is the number of types of "aggregate Key".\n - The number of columns of TSV is 5 of "key", "sum", "percentage", "count", and "average".\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- Only "DESC" or "ASC" is valid for the sort config.\n - If no sort setting, it will be in the order of appearance.\n - Character sorting is in code order.\n - If both character sort and numeric sort are configured, numeric sort is performed after character sort.\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- \u51fa\u529b\u3055\u308c\u308bTSV\u30c6\u30ad\u30b9\u30c8\uff08\u5408\u8a08\u5024\u30c6\u30fc\u30d6\u30ebTSV\u7b49\uff09\u306f\u3001\u77e9\u5f62\u30c7\u30fc\u30bf\u3067\u3059\u3002\n - TSV\u306e\u884c\u6570\u306f "\u96c6\u7d04Key" \u306e\u7a2e\u985e\u6570\u306b\u306a\u308a\u307e\u3059\u3002\n - TSV\u306e\u5217\u6570\u306f\u3001"\u96c6\u7d04Key\u5217" "\u5408\u7b97\u5024\u5217" "\u5272\u5408\u5024\u5217" "\u500b\u6570\u5217" "\u5e73\u5747\u5024\u5217" \u306e\uff15\u3067\u3059\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- \u30bd\u30fc\u30c8\u8a2d\u5b9a\u306f "DESC" \u3082\u3057\u304f\u306f "ASC" \u306e\u307f\u304c\u6709\u52b9\u3067\u3059\u3002\n - \u30bd\u30fc\u30c8\u8a2d\u5b9a\u304c\u306a\u3044\u5834\u5408\u3001\u51fa\u73fe\u9806\u306b\u306a\u308a\u307e\u3059\u3002\n - \u6587\u5b57\u30bd\u30fc\u30c8\u306f\u6587\u5b57\u30b3\u30fc\u30c9\u9806\u306b\u306a\u308a\u307e\u3059\u3002\n - \u6587\u5b57\u30bd\u30fc\u30c8\u3068\u6570\u5024\u30bd\u30fc\u30c8\u304c\u3069\u3061\u3089\u3082\u8a2d\u5b9a\u3055\u308c\u305f\u5834\u5408\u3001\u6587\u5b57\u30bd\u30fc\u30c8\u306e\u5f8c\u306b\u6570\u5024\u30bd\u30fc\u30c8\u304c\u5b9f\u884c\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-Create-Summary-Table-for-Numeric-Column-2021.xml<\/a><\/li><\/ul>\n\n\n\n
2021-09-06 (C) Questetra, Inc. (MIT License)
https:\/\/support.questetra.com\/addons\/tsv-string-create-summary-table-for-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 stored in String-type data will be loaded automatically.
- 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.
-\u3000The output TSV text (total table TSV, etc.) is rectangular data.<\/li> - The number of rows in the TSV will be the number of “Aggregate Key” types.<\/li>
- The number of columns in the TSV is 5: key, sum, percentage, count, and average.<\/li><\/ul><\/li><\/ul>\n\n\n\n
<\/i> Capture<\/h3>\n\n\n\n
![\"Aggregates](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/09\/tsv-string-create-summary-table-for-numeric-column-2021-capture-en.png?resize=1200%2C675&ssl=1\")
<\/a><\/figure>\n\n\n\n![\"Aggregates](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/09\/tsv-string-create-summary-table-for-numeric-column-2021-config-en-711x630.png?resize=711%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 the end) are ignored.<\/li><\/ul><\/li>
- Only “DESC” or “ASC” is valid for the sort config.
- If there is no sort setting, it will be ordered by appearance.<\/li>
- Character sorting is in code order.<\/li>
- If both character sort and numeric sort are configured, numeric sort is performed after character sort.<\/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 commas are present in the numeric field, they are considered a thousands separator (The removed string will be 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.
- When the process reaches this automated task, TSVs stored in String-type data will be loaded automatically.
- <\/i><\/span> B1: Set Column ID of Numeric Field (eg “3” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n