TSV String, Calc Changes in Two Numeric Columns<\/div>
Calculates the difference and rate of change for two numeric columns in a TSV. Adds the calculated value to the rightmost column. It realizes month-on-month and year-on-year automation.<\/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 Base Field (eg “3” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> B2: Set Column ID of Target Field (eg “4” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n
- <\/i><\/span> C1: Select STRING that stores TSV with Calced Columns (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\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 strBaseId = configs.get( "StrConfB1" ); \/\/\/ REQUIRED \/\/\/\/\/\/\/\/\/\/\/\/\/\/\n if( strBaseId === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B1: BaseId} is empty \\n" );\n }\n const numBaseId = parseInt( strBaseId, 10 );\n if( isNaN( numBaseId ) || numBaseId < 0 ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B1: BaseId} must be a positive integer \\n" );\n }\n if( numBaseId >= arr2dTsv[0].length ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B1: BaseId} is larger than TsvWidth \\n" );\n }\nconst strTargetId = configs.get( "StrConfB2" ); \/\/\/ REQUIRED \/\/\/\/\/\/\/\/\/\/\/\/\/\/\n if( strTargetId === "" ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B2: TargetId} is empty \\n" );\n }\n const numTargetId = parseInt( strTargetId, 10 );\n if( isNaN( numTargetId ) || numTargetId < 0 ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B2: TargetId} must be a positive integer \\n" );\n }\n if( numTargetId >= arr2dTsv[0].length ){\n throw new Error( "\\n AutomatedTask ConfigError:" +\n " Config {B2: TargetId} is larger than TsvWidth \\n" );\n }\nconst strPocketOutput = configs.getObject( "SelectConfC1" ); \/\/\/ REQUIRED \/\/\/\/\/\/\/\/\/\/\/\/\/\/\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\/\/ compare Target field with Base field\nlet strOutput = "";\nfor( let i = 0; i < arr2dTsv.length; i++ ){\n let numTarget = parseFloat( arr2dTsv[i][numTargetId].replace(\/,\/g, '') );\n if( isNaN(numTarget) ){ numTarget = 0; }\n let numBase = parseFloat( arr2dTsv[i][numBaseId].replace(\/,\/g, '') );\n if( isNaN(numBase) ){ numBase = 0; }\n\n let numDiff = numTarget - numBase;\n let strRate = (numTarget \/ numBase).toFixed(3);\n strOutput += arr2dTsv[i].join("\\t") + "\\t";\n strOutput += numDiff + "\\t";\n strOutput += strRate;\n strOutput += "\\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 );\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 total, monthly access total, etc.\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- Two columns will be added on the right. (Difference \/ Rate)\n - Diff: {B2: Target field} - {B1: Base field}\n - Rate: {B2: Target field} \/ {B1: Base field}\n - The number of digits to appear after the decimal point is 3.\n- Set the fields with the column ID (starting with zero).\n - String that cannot be parsed by JavaScript `parseFloat()` are considered zero.\n- To delete unnecessary column, place "TSV String, Convert" etc. downstream of the workflow.\n - https:\/\/support.questetra.com\/addons\/tsv-string-convert-2021\/\n\nAPPENDIX:\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- The number of cells in output TSV (combined TSV) is uniform for each row.\n - If the number of cells in TsvA is not uniform in each row, blank strings will be added.\n- Division by zero is output as `Infinity` or `NaN`.\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\u96c6\u8a08\u3001\u6708\u6b21\u30a2\u30af\u30bb\u30b9\u96c6\u8a08\u3001\u306a\u3069\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- \u6700\u3082\u53f3\u5074\u306b\uff12\u5217\u304c\u8ffd\u52a0\u3055\u308c\u307e\u3059\u3002\uff08\u5dee\u5206\u30fb\u5909\u5316\u7387\uff09\n - \u5dee\u5206\uff1a {B2:\u8a08\u6e2c\u5024\u30d5\u30a3\u30fc\u30eb\u30c9} - {B1:\u57fa\u6e96\u5024\u30d5\u30a3\u30fc\u30eb\u30c9}\n - \u5909\u5316\u7387\uff1a {B2:\u8a08\u6e2c\u5024\u30d5\u30a3\u30fc\u30eb\u30c9} \/ {B1:\u57fa\u6e96\u5024\u30d5\u30a3\u30fc\u30eb\u30c9}\n - \u5c0f\u6570\u70b9\u4ee5\u4e0b\u306e\u8868\u793a\u6841\u6570\u306f3\u6841\u3067\u3059\u3002\n- \u30d5\u30a3\u30fc\u30eb\u30c9\u5217\u306f\u3001\u5217ID\uff08\u30bc\u30ed\u59cb\u307e\u308a\uff09\u3067\u8a2d\u5b9a\u3057\u3066\u304f\u3060\u3055\u3044\u3002\n - JavaScript `parseFloat()` \u3067\u89e3\u6790\u3067\u304d\u306a\u3044\u6587\u5b57\u5217\u306f\u30bc\u30ed\u3068\u898b\u306a\u3055\u308c\u307e\u3059\u3002\n- \u4e0d\u8981\u306a\u30c7\u30fc\u30bf\u5217\u306f\u300eTSV \u6587\u5b57\u5217, \u5909\u63db\u300f\u7b49\u3092\u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u4e0b\u6d41\u306b\u914d\u7f6e\u3057\u3066\u524a\u9664\u3057\u307e\u3059\u3002\n - https:\/\/support.questetra.com\/ja\/addons\/tsv-string-convert-2021\/\n\nAPPENDIX-ja:\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 - \u6700\u7d42\u884c\u306e\u6539\u884c\u30b3\u30fc\u30c9\u3082\u4ed8\u4e0e\u3055\u308c\u307e\u305b\u3093\u3002\n- \u51fa\u529b\u3055\u308c\u308bTSV\uff08\u7d50\u5408\u3055\u308c\u305fTSV\uff09\u306e\u30bb\u30eb\u6570\u306f\u5404\u884c\u3067\u5747\u4e00\u3068\u306a\u308a\u307e\u3059\u3002\n - TsvA\u306e\u30bb\u30eb\u6570\u304c\u5404\u884c\u3067\u5747\u4e00\u3067\u306a\u304b\u3063\u305f\u5834\u5408\u3001\u7a7a\u6587\u5b57\u304c\u8ffd\u52a0\u3055\u308c\u307e\u3059\u3002\n- 0 \u306e\u9664\u7b97\u306f `Infinity` \u3084 `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-Calc-Changes-in-Two-Numeric-Columns-2021.xml<\/a><\/li>\n<\/ul>\n\n\n\n
2021-08-30 (C) Questetra, Inc. (MIT License)
https:\/\/support.questetra.com\/addons\/tsv-string-calc-changes-in-two-numeric-columns-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
- \n
- When the process reaches this automated task, the TSV is automatically read.\n
- \n
- TSV: Monthly sales total, monthly access total, etc.<\/li>\n<\/ul>\n<\/li>\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
- Two columns will be added on the right. (Difference \/ Rate)\n
- \n
- Diff: {B2: Target field} – {B1: Base field}<\/li>\n\n\n\n
- Rate: {B2: Target field} \/ {B1: Base field}\n
- \n
- The number of digits to appear after the decimal point is 3.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Set the fields with the column ID (starting with zero).\n
- \n
- Strings that cannot be parsed by JavaScript
parseFloat()<\/code> are considered to be zero.<\/li>\n<\/ul>\n<\/li>\n\n\n\n- To delete unnecessary columns, place “TSV String, Convert” etc. downstream in the workflow.\n
- \n
- https:\/\/support.questetra.com\/addons\/tsv-string-convert-2021\/<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
<\/i> Capture<\/h3>\n\n\n\n
![\"Calculates](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/09\/tsv-string-calc-changes-in-two-numeric-columns-2021-capture-en.png.png?resize=1200%2C675&ssl=1\")
<\/a><\/figure>\n\n\n\n![\"Calculates](\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/09\/tsv-string-calc-changes-in-two-numeric-columns-2021-config-en-953x630.png?resize=953%2C630&ssl=1\")
<\/a><\/figure>\n\n\n\n<\/i> Appendix<\/h3>\n\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.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- The number of cells in the (merged) output TSV is uniform for each row.\n
- \n
- If the number of cells in TsvA is not uniform in each row, blank strings will be added.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Division by zero is output as
Infinity<\/code> orNaN<\/code>.<\/li>\n<\/ul>\n\n\n\n<\/i> See also<\/h3>\n\n\n\n
- If there is a blank line in the input TSV text, it will be skipped.\n
- To delete unnecessary columns, place “TSV String, Convert” etc. downstream in the workflow.\n
- Strings that cannot be parsed by JavaScript
- When the process reaches this automated task, the TSV is automatically read.\n
- <\/i><\/span> B1: Set Column ID of Base Field (eg “3” ) *<\/span>#{EL}<\/sup><\/span><\/li>\n