TSV String, Create Cross Table for Numeric Column<\/div>
Aggregates the values in a numeric column with 2 aggregation-key columns (pivot table). Sum, Percentage, Count and Average are tabulated as a cross table TSV. E.g. “Sales for each combination of Client-Y and Store-X” is aggregated from “Sales log TSV”.<\/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 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 Numeric Value, 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 Total Cross TSV (update)<\/li>\n
- <\/i><\/span> D2: Select STRING DATA that stores Percent Cross TSV (update)<\/li>\n
- <\/i><\/span> D3: Select STRING DATA that stores Count Cross TSV (update)<\/li>\n
- <\/i><\/span> D4: Select STRING DATA that stores Average 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 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 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 strPocketTotalPivot = configs.getObject( "SelectConfD1" ); \/\/ NotRequired \/\/\/\/\/\nconst strPocketPercentPivot = configs.getObject( "SelectConfD2" ); \/\/ NotRequired \/\/\/\/\/\nconst strPocketCountPivot = configs.getObject( "SelectConfD3" ); \/\/ NotRequired \/\/\/\/\/\nconst strPocketAveragePivot = configs.getObject( "SelectConfD4" ); \/\/ 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 arr2dTotalPivot[y][x]\nlet arr2dTotalPivot = [];\nfor( let i = 0; i < arrYaggKeys.length; i++ ){\n arr2dTotalPivot[i] = [];\n for( let j = 0; j < arrXaggKeys.length; j++ ){\n arr2dTotalPivot[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\/\/ 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 arr2dAveragePivot[y][x]\nlet arr2dAveragePivot = [];\nfor( let i = 0; i < arrYaggKeys.length; i++ ){\n arr2dAveragePivot[i] = [];\n for( let j = 0; j < arrXaggKeys.length; j++ ){\n arr2dAveragePivot[i][j] = 0;\n }\n}\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 strYKey = arr2dTsv[i][numYaggField];\n let strXKey = arr2dTsv[i][numXaggField];\n arr2dTotalPivot[ arrYaggKeys.indexOf( strYKey ) ][ arrXaggKeys.indexOf( strXKey ) ] += numValue;\n arr2dCountPivot[ arrYaggKeys.indexOf( strYKey ) ][ arrXaggKeys.indexOf( strXKey ) ] += 1;\n numGrandTotal += numValue;\n}\n\n\/\/\/\/ --debug--\n\/\/ engine.log( " arr2dTotalPivot:" );\n\/\/ for( let i = 0; i < arr2dTotalPivot.length; i++ ){\n\/\/ for( let j= 0; j < arr2dTotalPivot[0].length; j++ ){\n\/\/ engine.log( " " + i + "-" + j + ": " + arr2dTotalPivot[i][j] );\n\/\/ }\n\/\/ }\n\n\/\/ X-subtotal Total\nlet arrXsubtotalTotal = [];\n arrXsubtotalTotal.push( "total" );\nfor( let i = 0; i < arr2dTotalPivot[0].length; i++ ){\n let numSubtotal = 0;\n for( let j = 0; j < arr2dTotalPivot.length; j++ ){\n numSubtotal += arr2dTotalPivot[j][i];\n }\n arrXsubtotalTotal.push( numSubtotal );\n}\narrXsubtotalTotal.push( numGrandTotal );\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 Total\nfor( let i = 0; i < arr2dTotalPivot.length; i++ ){\n let numSubtotal = 0;\n for( let j = 0; j < arr2dTotalPivot[i].length; j++ ){\n numSubtotal += arr2dTotalPivot[i][j];\n }\n arr2dTotalPivot[i].push( numSubtotal ); \/\/ add Y-total to the end of an array\n arr2dTotalPivot[i].unshift( arrYaggKeys[i] ); \/\/ add Y-key to the beginning of an array\n}\n\/\/ 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 );\n arr2dCountPivot[i].unshift( arrYaggKeys[i] );\n}\n\n\/\/ sort by Y-subtotal\nif( strNumSort === "ASC" ){ \/\/ ASC: ascending alphabetical from 0 to 10\n arr2dTotalPivot.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 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 arr2dTotalPivot.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 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 and Average\nfor( let i = 0; i < arr2dTotalPivot.length; i++ ){\n arr2dPercentPivot[i][0] = arr2dTotalPivot[i][0]; \/\/ Y-key\n arr2dAveragePivot[i][0] = arr2dTotalPivot[i][0];\n for( let j = 1; j < arr2dTotalPivot[0].length; j++ ){\n arr2dPercentPivot[i][j] = (arr2dTotalPivot[i][j] \/ numGrandTotal).toFixed(3);\n arr2dAveragePivot[i][j] = (arr2dTotalPivot[i][j] \/ arr2dCountPivot[i][j]).toFixed(3);\n }\n}\n\n\/\/ X-subtotal Percent and Average\nlet arrXsubtotalPercent = [];\nlet arrXsubtotalAverage = [];\n arrXsubtotalPercent.push( "total" );\n arrXsubtotalAverage.push( "total" );\nfor( let i = 1; i < arrXsubtotalTotal.length; i++ ){\n arrXsubtotalPercent.push( (arrXsubtotalTotal[i] \/ numGrandTotal).toFixed(3) );\n arrXsubtotalAverage.push( (arrXsubtotalTotal[i] \/ arrXsubtotalCount[i] ).toFixed(3) );\n}\n\n\/\/ output Cross Tabulation\nlet strTotalPivot = "";\nstrTotalPivot += "cross\\t" + arrXaggKeys.join("\\t") + "\\ttotal\\n";\nfor( let i = 0; i < arr2dTotalPivot.length; i++ ){\n strTotalPivot += arr2dTotalPivot[i].join("\\t") + "\\n";\n}\nstrTotalPivot += arrXsubtotalTotal.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\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 strAveragePivot = "";\nstrAveragePivot += "cross\\t" + arrXaggKeys.join("\\t") + "\\ttotal\\n";\nfor( let i = 0; i < arr2dAveragePivot.length; i++ ){\n strAveragePivot += arr2dAveragePivot[i].join("\\t") + "\\n";\n}\nstrAveragePivot += arrXsubtotalAverage.join("\\t");\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nif( strPocketTotalPivot !== null ){\n engine.setData( strPocketTotalPivot, strTotalPivot );\n}\nif( strPocketPercentPivot !== null ){\n engine.setData( strPocketPercentPivot, strPercentPivot );\n}\nif( strPocketCountPivot !== null ){\n engine.setData( strPocketCountPivot, strCountPivot );\n}\nif( strPocketAveragePivot !== null ){\n engine.setData( strPocketAveragePivot, strAveragePivot );\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, log for questionnaire with rating, 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- 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- Division by zero is output as NaN.\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\u8a55\u70b9\u4ed8\u304d\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- \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- 0 \u306e\u9664\u7b97\u306f NaN \u3068\u51fa\u529b\u3055\u308c\u307e\u3059\u3002\n*\/\n\n<\/code><\/pre><\/div>\n\n\n<\/div><\/div>\n\n\n\n<\/a><\/figure>\n\n\n\n
<\/i> Download<\/h3>\n\n\n\n
- \n
- Tsv-String-Create-Cross-Table-for-Numeric-Column-2021.xml<\/a>\n
- \n
- 2021-09-07 (C) Questetra, Inc. (MIT License)<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Tsv-String-Create-Cross-Table-for-Numeric-Column-2023.xml<\/a>\n
- \n
- 2023-08-28 (C) Questetra, Inc. (MIT License)<\/li>\n\n\n\n
- for “GraalJS standard (engine-type 3)” on v15.0 or above<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
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
- \n
- When the process reaches this automated task, the TSV is automatically read.\n
- \n
- TSV: Monthly sales, log for questionnaire with rating, etc.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- If there is a blank line in the input TSV text, it will be skipped.\n
- \n
- The line feed code for the last line is not added either.
-\u3000The output TSV text (total table TSV, etc.) is rectangular data.<\/li>\n\n\n\n - The number of rows in TSV is #of {Y aggregate Key types} plus 2 (“header” + “total”).<\/li>\n\n\n\n
- The number of columns of TSV is #of {X aggregate Key types} plus 2 (“heading” + “total”).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
<\/i> Capture<\/h3>\n\n\n\n
![\"No](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/09\/tsv-string-create-cross-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-cross-table-for-numeric-column-2021-config-en-654x630.png?resize=654%2C630&ssl=1\")
<\/a><\/figure>\n\n\n\n<\/i> Appendix<\/h3>\n\n\n\n
- \n
- TSV (Tab Separated Values) text assumes rectangular data.\n
- \n
- Data that is not rectangular is automatically formatted with empty characters.<\/li>\n\n\n\n
- Blank lines (including the end) are ignored.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Only “DESC” or “ASC” is valid for the sort config.\n
- \n
- If no sort settings are specified, it will be sorted in order of appearance.<\/li>\n\n\n\n
- Character sorting is in code order.<\/li>\n\n\n\n
- If both character sort and numeric sort are configured, numeric sort is performed after character sort.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Specify the field column with the column ID (starting with zero).\n
- \n
- Parsing numeric field values depends on JavaScript
parseFloat()<\/code>.<\/li>\n\n\n\n- If there are commas in the numeric field, they are considered as thousands separators (The removed string is parsed).<\/li>\n\n\n\n
- A string that cannot be parsed is considered zero.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Division by zero is output as
NaN<\/code>.<\/li>\n<\/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.\n
- The line feed code for the last line is not added either.
- When the process reaches this automated task, the TSV is automatically read.\n
- <\/i><\/span> B1: Set Column ID of Numeric Field (eg “3” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n