TSV String, Create Cross Table of Count<\/div>
Aggregates the count of data by 2 aggregation-key columns (pivot table). Count and Percentage are tabulated as a cross table TSV. E.g. Survey Results TSV is automatically generated from Survey Records TSV. 2D frequency distribution.<\/div><\/div>\n\n\n
<\/span>Configs<\/div>
\n
- \n
- <\/i><\/span> A1: Set TSV *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> B2: Set Column ID of Aggregation-Key Y-Field (eg “4” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> B3: Set Column ID of Aggregation-Key X-Field (eg “5” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C1: To Sort by Count, Set DESC or ASC (eg “ASC” )#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C2: To Sort by Y-Agg-Key, Set DESC or ASC (eg “ASC” )#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C3: To Sort by X-Agg-Key, Set DESC or ASC (eg “ASC” )#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> D1: Select STRING DATA that stores Count Cross TSV (update)<\/li>\n
- <\/i><\/span> D2: Select STRING DATA that stores Percent Cross TSV (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 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 strXaggField = configs.get( "StrConfB3" ); \/\/\/ REQUIRED \/\/\/\/\/\/\/\/\/\/\/\/\/\n if( strXaggField === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B3: X-AggField} is empty \\n" );\n }\n const numXaggField = parseInt( strXaggField, 10 );\n if( isNaN( numXaggField ) || numXaggField < 0 ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B3: XaggField} must be a positive integer \\n" );\n }\n if( numXaggField >= arr2dTsv[0].length ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B3: XaggField} is larger than TsvWidth \\n" );\n }\nconst strNumSort = configs.get( "StrConfC1" ); \/\/ NotRequired \/\/\/\/\/\/\/\/\/\/\/\nconst strYaggSort = configs.get( "StrConfC2" ); \/\/ NotRequired \/\/\/\/\/\/\/\/\/\/\/\nconst strXaggSort = configs.get( "StrConfC3" ); \/\/ NotRequired \/\/\/\/\/\/\/\/\/\/\/\n\nconst strPocketCountPivot = configs.getObject( "SelectConfD1" ); \/\/ NotRequired \/\/\/\/\/\nconst strPocketPercentPivot = 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\/\/ omit repeated Keys in X-Aggregation Field values (make Keys uniq)\nlet arrXaggKeys = [];\nfor( let i = 0; i < arr2dTsv.length; i++ ){\n if( arrXaggKeys.indexOf( arr2dTsv[i][ numXaggField ] ) === -1){\n arrXaggKeys.push( arr2dTsv[i][ numXaggField ] );\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}\nif( strXaggSort === "ASC" ){ \/\/ ASC: ascending alphabetical from A to Z\n arrXaggKeys.sort( function( strA, strB ){\n if( strA > strB ){ return 1; }\n if( strA < strB ){ return -1; }\n return 0;\n });\n}\nif( strXaggSort === "DESC" ){ \/\/ DESC: descending from Z to A\n arrXaggKeys.sort( function( strA, strB ){\n if( strA < strB ){ return 1; }\n if( strA > strB ){ return -1; }\n return 0;\n });\n}\n\n\/\/ initialize arr2dCountPivot[y][x]\nlet arr2dCountPivot = [];\nfor( let i = 0; i < arrYaggKeys.length; i++ ){\n arr2dCountPivot[i] = [];\n for( let j = 0; j < arrXaggKeys.length; j++ ){\n arr2dCountPivot[i][j] = 0;\n }\n}\n\/\/ initialize arr2dPercentPivot[y][x]\nlet arr2dPercentPivot = [];\nfor( let i = 0; i < arrYaggKeys.length; i++ ){\n arr2dPercentPivot[i] = [];\n for( let j = 0; j < arrXaggKeys.length; j++ ){\n arr2dPercentPivot[i][j] = 0;\n }\n}\n\n\/\/ count up\nfor( let i = 0; i < arr2dTsv.length; i++ ){\n let strYKey = arr2dTsv[i][numYaggField];\n let strXKey = arr2dTsv[i][numXaggField];\n arr2dCountPivot[ arrYaggKeys.indexOf( strYKey ) ][ arrXaggKeys.indexOf( strXKey ) ] += 1;\n}\n\n\/\/ X-subtotal Count\nlet arrXsubtotalCount = [];\n arrXsubtotalCount.push( "total" );\nfor( let i = 0; i < arr2dCountPivot[0].length; i++ ){\n let numSubtotal = 0;\n for( let j = 0; j < arr2dCountPivot.length; j++ ){\n numSubtotal += arr2dCountPivot[j][i];\n }\n arrXsubtotalCount.push( numSubtotal );\n}\narrXsubtotalCount.push( arr2dTsv.length );\n\n\/\/ add Y-key and Y-subtotal Count\nfor( let i = 0; i < arr2dCountPivot.length; i++ ){\n let numSubtotal = 0;\n for( let j = 0; j < arr2dCountPivot[i].length; j++ ){\n numSubtotal += arr2dCountPivot[i][j];\n }\n arr2dCountPivot[i].push( numSubtotal ); \/\/ add Y-total to the end of an array\n arr2dCountPivot[i].unshift( arrYaggKeys[i] ); \/\/ add Y-key to the beginning of an array\n}\n\n\/\/ sort by Y-subtotal\nif( strNumSort === "ASC" ){ \/\/ ASC: ascending alphabetical from 0 to 10\n arr2dCountPivot.sort( function( arrA, arrB ){\n if( arrA[arrA.length-1] > arrB[arrB.length-1] ){ return 1; }\n if( arrA[arrA.length-1] < arrB[arrB.length-1] ){ return -1; }\n return 0;\n });\n}\nif( strNumSort === "DESC" ){ \/\/ DESC: descending from 10 to 0\n arr2dCountPivot.sort( function( arrA, arrB ){\n if( arrA[arrA.length-1] < arrB[arrB.length-1] ){ return 1; }\n if( arrA[arrA.length-1] > arrB[arrB.length-1] ){ return -1; }\n return 0;\n });\n}\n\n\/\/ calc Percent\nfor( let i = 0; i < arr2dCountPivot.length; i++ ){\n arr2dPercentPivot[i][0] = arr2dCountPivot[i][0]; \/\/ Y-key\n for( let j = 1; j < arr2dCountPivot[0].length; j++ ){\n arr2dPercentPivot[i][j] = (arr2dCountPivot[i][j] \/ arr2dTsv.length).toFixed(3);\n }\n}\n\n\/\/ X-subtotal Percent and Average\nlet arrXsubtotalPercent = [];\n arrXsubtotalPercent.push( "total" );\nfor( let i = 1; i < arrXsubtotalCount.length; i++ ){\n arrXsubtotalPercent.push( (arrXsubtotalCount[i] \/ arr2dTsv.length).toFixed(3) );\n}\n\n\/\/ output Cross Tabulation\nlet strCountPivot = "";\nstrCountPivot += "cross\\t" + arrXaggKeys.join("\\t") + "\\ttotal\\n";\nfor( let i = 0; i < arr2dCountPivot.length; i++ ){\n strCountPivot += arr2dCountPivot[i].join("\\t") + "\\n";\n}\nstrCountPivot += arrXsubtotalCount.join("\\t");\n\nlet strPercentPivot = "";\nstrPercentPivot += "cross\\t" + arrXaggKeys.join("\\t") + "\\ttotal\\n";\nfor( let i = 0; i < arr2dPercentPivot.length; i++ ){\n strPercentPivot += arr2dPercentPivot[i].join("\\t") + "\\n";\n}\nstrPercentPivot += arrXsubtotalPercent.join("\\t");\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nif( strPocketCountPivot !== null ){\n engine.setData( strPocketCountPivot, strCountPivot );\n}\nif( strPocketPercentPivot !== null ){\n engine.setData( strPocketPercentPivot, strPercentPivot );\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\/*\nTSV Example:\n2021-08\tOrange\t200\n2021-08\tApple\t1,200\n2021-09\tApple\t1,100\n2021-09\tOrange\t200\n2021-09\tTomato\t500\n2021-09\tOrange\t200\n2021-09\tTomato\t500\n2021-10\tTomato\t500\n2021-10\tTomato\t500\n2021-10\tTomato\t450\n2021-10\tTomato\t450\n\n\nNotes:\n- When the process reaches this automated task, TSV is automatically read.\n - TSV: Sales Records, Survey Records, 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 #of {Y aggregate Key types} plus 2 ("header" + "total").\n - The number of columns of TSV is #of {X aggregate Key types} plus 2 ("heading" + "total").\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\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: \u58f2\u4e0a\u30ec\u30b3\u30fc\u30c9\u3001\u30a2\u30f3\u30b1\u30fc\u30c8\u306e\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\u3001{Y\u96c6\u7d04Key\u306e\u7a2e\u985e\u6570} \u306b\uff12\uff08"\u30d8\u30c3\u30c0\u884c" + "\u5408\u8a08\u884c"\uff09\u3092\u52a0\u3048\u305f\u6570\u306b\u306a\u308a\u307e\u3059\u3002\n - TSV\u306e\u5217\u6570\u306f\u3001{X\u96c6\u7d04Key\u306e\u7a2e\u985e\u6570} \u306b\uff12\uff08"\u898b\u51fa\u3057\u5217" + "\u5408\u8a08\u5217"\uff09\u3092\u52a0\u3048\u305f\u6570\u306b\u306a\u308a\u307e\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*\/\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-Cross-Table-of-Count-2021.xml<\/a><\/li><\/ul>\n\n\n\n
2021-09-12 (C) Questetra, Inc. (MIT License)
https:\/\/support.questetra.com\/addons\/tsv-string-create-cross-table-of-count-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, the TSV is read automatically.
- TSV: Sales Records, Survey Records, 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 TSV is #of {Y aggregate Key types} plus 2 (“header” + “total”).<\/li>
- The number of columns of TSV is #of {X aggregate Key types} plus 2 (“heading” + “total”).<\/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-cross-table-of-count-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-cross-table-of-count-2021-config-en-658x630.png?resize=658%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 no sort setting is specified, it will be sorted in the order of 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><\/ul>\n\n\n\n
<\/i> See also<\/h3>\n\n\n\n
- 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, the TSV is read automatically.
- <\/i><\/span> B2: Set Column ID of Aggregation-Key Y-Field (eg “4” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n