{"id":109999,"date":"2021-07-21T12:19:11","date_gmt":"2021-07-21T03:19:11","guid":{"rendered":"https:\/\/support.questetra.com\/?p=109999"},"modified":"2021-10-05T16:41:24","modified_gmt":"2021-10-05T07:41:24","slug":"tsv-string-create-cross-tab-for-numerical-sum-2021","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/en\/addons\/tsv-string-create-cross-tab-for-numerical-sum-2021\/","title":{"rendered":"TSV String, Create Cross Tab for Numerical Sum"},"content":{"rendered":"<div class=\"su-note\"  style=\"border-color:#e5dab2;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\"><div class=\"su-note-inner su-u-clearfix su-u-trim\" style=\"background-color:#FFF4CC;border-color:#ffffff;color:#333333;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\">\n<h3><i class=\"fal fa-exclamation-circle\"><\/i> PAGE UPDATED<\/h3>\n<div style=\"text-align: center;\"><i class=\"fal fa-truck fa-lg\"><\/i> <a href=\"https:\/\/support.questetra.com\/en\/addons\/tsv-string-create-cross-table-for-numeric-column-2021\/\">https:\/\/support.questetra.com\/addons\/tsv-string-create-cross-table-for-numeric-column-2021\/<\/a><\/div>\n<\/div><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><div class=\"su-box su-box-style-default\" id=\"\" style=\"border-color:#003e04;border-radius:0px;max-width:none\"><div class=\"su-box-title\" style=\"background-color:#267137;color:#ffffff;border-top-left-radius:0px;border-top-right-radius:0px\">TSV String, Create Cross Tab for Numerical Sum<\/div><div class=\"su-box-content su-u-clearfix su-u-trim\" style=\"border-bottom-left-radius:0px;border-bottom-right-radius:0px\"> Creates a crosstab table (or pivot table) for the numeric fields of TSV data. For example, the &#8220;Amount&#8221; in the order history (TSV) is aggregated on the two axes of &#8220;Person Responsible&#8221; and &#8220;Product&#8221;. The fields for X-axis and Y-axis aggregation are specified by the TSV field ID.<\/div><\/div>\n\n\n<div class=\"su-spoiler su-spoiler-style-modern-light su-spoiler-icon-plus-square-1\" data-anchor=\"configs\" data-scroll-offset=\"0\" data-anchor-in-url=\"no\"><div class=\"su-spoiler-title\" tabindex=\"0\" role=\"button\"><span class=\"su-spoiler-icon\"><\/span>Configs<\/div><div class=\"su-spoiler-content su-u-clearfix su-u-trim\">\n<ul class=\"fa-ul\">\n<li> A: Select STRING DATA for TSV String<span style=\"color:#990000;\"> *<\/span><\/li>\n<li><span class=\"fa-li\"><i class=\"far fa-pen-square fa-lg\"><\/i><\/span> B-target: Set Col-IDs for Sum, X-flds and Y-flds (eg &#8220;3,0,2&#8221;)<span style=\"color:#990000;\"> *<\/span><span style=\"color:#000099;\"><sup style=\"font-style:italic;\">#{EL}<\/sup><\/span><\/li>\n<li><span class=\"fa-li\"><i class=\"far fa-pen-square fa-lg\"><\/i><\/span> B-order: Set DESC or ASC (default &#8220;DESC&#8221;)<span style=\"color:#000099;\"><sup style=\"font-style:italic;\">#{EL}<\/sup><\/span><\/li>\n<li> C: Select STRING DATA for newly generated Crosstab TSV (update)<span style=\"color:#990000;\"> *<\/span><\/li>\n<\/ul>\n<\/div><\/div>\n\n\n<div class=\"su-spoiler su-spoiler-style-modern-light su-spoiler-icon-plus-square-1 su-spoiler-closed\" data-anchor=\"script\" data-scroll-offset=\"0\" data-anchor-in-url=\"no\"><div class=\"su-spoiler-title\" tabindex=\"0\" role=\"button\"><span class=\"su-spoiler-icon\"><\/span>Script (click to open)<\/div><div class=\"su-spoiler-content su-u-clearfix su-u-trim\">\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-js\" data-lang=\"JavaScript\"><code>\/\/ GraalJS Script (engine type: 2)\n\/\/ (c) 2021, Questetra, Inc. (the MIT License)\n\/\/ Notes:\n\/\/ A timeout may occur depending on the number of records or aggregate labels.\n\/\/ Aggregate labels are sorted in ascending or descending order.\n\/\/ Numeric parsing depends on JavaScript parseFloat().\n\/\/ If a &quot;prefix&quot; precedes a number, it will not be determined as a number. (eg &quot;$ 100&quot;)\n\/\/ The period &quot;.&quot; is recognized as a decimal point.\n\/\/ Assumes a digit separator and evaluates all numbers after removing all commas &quot;,&quot;.\n\n\/\/ Notes(ja):\n\/\/ \u5c65\u6b74TSV\u306e\u30ec\u30b3\u30fc\u30c9\u6570\uff08\u884c\u6570\uff09\u3084\u96c6\u7d04\u30e9\u30d9\u30eb\u306e\u6570\u306b\u3088\u3063\u3066\u306f\u30bf\u30a4\u30e0\u30a2\u30a6\u30c8\u3068\u306a\u308a\u307e\u3059\u3002\n\/\/ \u96c6\u7d04\u30e9\u30d9\u30eb\u306f\u6607\u9806\u3082\u3057\u304f\u306f\u964d\u9806\u3067\u30bd\u30fc\u30c8\u3055\u308c\u307e\u3059\u3002\n\/\/ \u6570\u5024\u5224\u5b9a\u306f JavaScript parseFloat() \u306b\u4f9d\u5b58\u3057\u307e\u3059\n\/\/ \u6570\u5024\u306e\u524d\u306b &quot;\u63a5\u982d\u8f9e&quot; \u304c\u3042\u308b\u3068\u6570\u5024\u3068\u3057\u3066\u5224\u5b9a\u3055\u308c\u307e\u305b\u3093 ( \u4f8b &quot;$100&quot; )\n\/\/ \u30d4\u30ea\u30aa\u30c9 &quot;.&quot; \u306f\u5c0f\u6570\u70b9\u3068\u3057\u3066\u8a8d\u8b58\u3055\u308c\u307e\u3059\n\/\/ \u6841\u533a\u5207\u308a\u6587\u5b57\u3092\u60f3\u5b9a\u3057\u3001\u5168\u3066\u306e\u30ab\u30f3\u30de &quot;,&quot; \u3092\u9664\u53bb\u3057\u305f\u3046\u3048\u3067\u6570\u5024\u5224\u5b9a\u3057\u307e\u3059\n\n\n\/\/\/\/\/\/\/\/ START &quot;main()&quot; \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\nmain();\nfunction main(){ \n\n\/\/\/\/ == Config Retrieving \/ \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 ==\nconst dataIdA      = configs.get( &quot;conf_DataIdA&quot; ) + &quot;&quot;;      \/\/ config required\nconst targetColIds = configs.get( &quot;conf_TargetColIds&quot; ) + &quot;&quot;; \/\/ config required\nlet   sortOrder    = configs.get( &quot;conf_SortOrder&quot; ) + &quot;&quot;;    \/\/ config not required\nconst dataIdC      = configs.get( &quot;conf_DataIdC&quot; ) + &quot;&quot;;      \/\/ config required\nengine.log( &quot; AutomatedTask Config: Crosstab Target Columns: &quot; + targetColIds );\nengine.log( &quot; AutomatedTask Config: Sort Order: &quot; + sortOrder );\n\nconst arrTargetColIds = targetColIds.split(&quot;,&quot;);\nif( arrTargetColIds.length !== 3 ){\n  throw new Error( &quot;\\n AutomatedTask ConfigError:&quot; +\n                   &quot; Config {Target Col-IDs} length must be three \\n&quot; );\n}\nconst intCalcTarget = parseInt( arrTargetColIds[0] );\nconst intXFieldCol  = parseInt( arrTargetColIds[1] );\nconst intYFieldCol  = parseInt( arrTargetColIds[2] );\n\nif( sortOrder !== &quot;ASC&quot; ){\n  sortOrder = &quot;DESC&quot;;\n}\n\n\n\/\/\/\/ == Data Retrieving \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u306e\u53c2\u7167 ==\nconst inputString = engine.findDataByNumber( dataIdA ) + &quot;&quot;;\nlet   arrInputString = inputString.split(&quot;\\n&quot;);\nengine.log( &quot; AutomatedTask MultilineString:&quot; + \n            &quot; TSV {A}, number of lines: &quot; + arrInputString.length );\n\n\n\/\/\/\/ == Calculating \/ \u6f14\u7b97 ==\n\/\/ Extract X-flds Y-flds\nlet arrXFields = [];\nlet arrYFields = [];\nfor( let i = 0; i &lt; arrInputString.length; i++ ){\n  let arrTsvLine = arrInputString[i].split(&quot;\\t&quot;);\n  if( arrXFields.indexOf( arrTsvLine[ intXFieldCol ] ) == -1){\n      arrXFields.push(    arrTsvLine[ intXFieldCol ] );\n  }\n  if( arrYFields.indexOf( arrTsvLine[ intYFieldCol ] ) == -1){\n      arrYFields.push(    arrTsvLine[ intYFieldCol ] );\n  }\n}\narrXFields.sort( function( strA, strB ){\n  if( sortOrder === &quot;DESC&quot; ){ \/\/ from Z to A\n    if( strA &lt; strB ){ return 1;}\n    if( strA &gt; strB ){ return -1;}\n  }else{ \/\/ ASC: alphabetical from A to Z\n    if( strA &gt; strB ){ return 1;}\n    if( strA &lt; strB ){ return -1;}\n  }\n});\narrYFields.sort( function( strA, strB ){\n  if( sortOrder === &quot;DESC&quot; ){ \/\/ from Z to A\n    if( strA &lt; strB ){ return 1;}\n    if( strA &gt; strB ){ return -1;}\n    return 0;\n  }else{ \/\/ ASC: alphabetical from A to Z\n    if( strA &gt; strB ){ return 1;}\n    if( strA &lt; strB ){ return -1;}\n    return 0;\n  }\n});\n\n\/\/ Calculate outputTable[y][x]\nlet grandTotal = 0;\nlet outputTable = new Array( arrYFields.length );\nfor( let i = 0; i &lt; arrYFields.length; i++ ){\n  outputTable[i] = new Array( arrXFields.length );\n  for( let j = 0; j &lt; arrXFields.length; j++ ){\n    outputTable[i][j] = 0; \/\/ initialize\n  }\n}\nfor( let i = 0; i &lt; arrInputString.length; i++ ){\n  let arrTsvLine = arrInputString[i].split(&quot;\\t&quot;);\n  let numValue = parseFloat( arrTsvLine[ intCalcTarget ].replace( \/,\/g,&quot;&quot;) );\n  if ( isNaN(numValue) ){\n    engine.log( &quot; AutomatedTask StringWarning:&quot; +\n                &quot; CellStr is not numeric at line: &quot; + i );\n    numValue = 0;\n  }\n  outputTable[\n    arrYFields.indexOf( arrTsvLine[ intYFieldCol ] )\n  ][\n    arrXFields.indexOf( arrTsvLine[ intXFieldCol ] )\n  ] += numValue;\n  grandTotal += numValue;\n}\n\n\/\/ Convert to TSV String\nlet outputTsv = &quot;Crosstab\\t&quot;;\nfor( let i = 0; i &lt; arrXFields.length; i++ ){\n  outputTsv += arrXFields[i] + &quot;\\t&quot;;\n}\noutputTsv += &quot;total\\n&quot;; \/\/ header ends\nfor( let i = 0; i &lt; outputTable.length; i++ ){\n  outputTsv += arrYFields[i] + &quot;\\t&quot;;\n  for( let j = 0; j &lt; outputTable[i].length; j++ ){\n    outputTsv += outputTable[i][j] + &quot;\\t&quot;;\n  }\n  outputTsv += sum( outputTable[i] ) + &quot;\\n&quot;;\n}\noutputTsv += &quot;total\\t&quot;;\nfor( let i = 0; i &lt; arrXFields.length; i++ ){\n  let vsum = 0;\n  for( let j = 0; j &lt; arrYFields.length; j++ ){\n    vsum += outputTable[j][i];\n  }\n  outputTsv += vsum + &quot;\\t&quot;;\n}\noutputTsv += grandTotal + &quot;&quot;;\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nengine.setDataByNumber( dataIdC, outputTsv );\n\n} \/\/\/\/\/\/\/\/ END &quot;main()&quot; \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\n\nfunction sum(arrF){\n  let tmp = 0;\n  for( let i = 0; i &lt; arrF.length; i++){ tmp += arrF[i]; }\n  return tmp;\n}\n\n<\/code><\/pre><\/div>\n\n\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"alignright\"><a href=\"#\"><img decoding=\"async\" src=\"data:image;base64,\niVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAADCUlEQVRYR82XT0iTcRjHP6\/ONV2i\nK7EQYaFCoHZQuugh1MRjh6BJUQfrsJkXg6CTHrwFYZ5yghVo5iHoFl6EQtRTJBaCmOBEQ0XM0s2J\n+\/PG713v3F73zm1ob7\/j8z7P9\/m+z9\/fT8LgI6Xsf4A8ZG4i0YhMDRKXANtf+21kPEjMIPMRifc4\n2UsF+3gCr7nIAU8AF2BJBRTYB9yYeUob68lskhNw40LiGWBN0bFWzYfMY1y49ez1CQzwAmjP0LHW\nrB8nDxNhJSbg5i0St0\/IeQRGZhQXd7SYRwmc7J8fG4l4ApGc9wur\/Jx8WkpbyDPlZRSIQDjA5Pok\nq77VeHuZ9tiaOCQQqfZFteBGr4\/SWt6KxPGNosdwdmsWx7iDhd8LsSo+zFSo3XGIPsBzoFPVnLs1\nR6WtMqO\/V412A7t0THYw\/H1Yi9OHk0dCGCEghgxsxfa5IFBkKWL8xzghOaSo2c\/aqb9Qz\/TGNMve\nZUUmUiRStenfZGpjKuqo2lZNRUGFHgExJ86LYRUh4OYuEnE0BQFxqt5VRUF7rvbQeaWTvm99dH\/u\nVuRNJU0MNQ4x\/2ue5g\/NUd3Ba4M4yh16BERX3MPFGzUCL4H7sXE6dQLwCicP1Ah8QaJGS+By4WX8\nQT8ysvLJJJnINeUqsqAcVGRZUpaShlA4hD\/kj0KYs8wchA+SRWAGF7VqBH7GLBYFRESg1FqKx+uJ\nghaYCyKyXQ++oE+RZ0vZlOWX4Q16Wdtbi+razDYKzxTqE4BtnJxTCUR+Meb8gxSAE+m\/IZAwBYlq\nwGKyKDWgtqYYVKIuZFlOrwbiUuAmYRHa8+0s7SwRJqwkJ1kN7If2WfGtRJNYbCnGmmNNuQgNb0PD\nB9GRUTxxY4K64rq4vIo5oFcDwXBQ6Xv1CN2dwA5tn9oYWxnTNplmFIvPmmXUUNJAV22Xsg8yOYLQ\nyOIIvV97E5lrlpFQ0azjTJymaKOzjoV1zIUkRbD01XQvJCqUoVcylYShl9LTiUSa1\/LDSBj4MFFJ\nGPo0i61xwx6n6TdaWhZ\/ADmdsjBbatgaAAAAAElFTkSuQmCC\n\" alt=\"\"\/><\/a><\/figure><\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-cloud-download-alt\"><\/i> Download<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/drive.google.com\/file\/d\/1ItAawWU56mO5DbVS0mKHX2DYPi5Xogv3\/view?usp=drivesdk\" target=\"_blank\" rel=\"noreferrer noopener\">Tsv-String-Create-Cross-Tab-for-Numerical-Sum-2021.xml<\/a><\/li><\/ul>\n\n\n\n<p class=\"has-text-align-right has-small-font-size wp-block-paragraph\">2021-07-16 (C) Questetra, Inc. (MIT License)<br><a href=\"https:\/\/support.questetra.com\/en\/addons\/tsv-string-create-cross-tab-for-numerical-sum-2021\/\">https:\/\/support.questetra.com\/addons\/tsv-string-create-cross-tab-for-numerical-sum-2021\/<\/a><br><i class=\"fal fa-info-circle\"><\/i> The Add-on import feature is available with <span style=\"color:#4a86e8\" class=\"has-inline-color\"><strong>Professional<\/strong><\/span> edition.<\/p>\n\n\n<div class=\"su-divider su-divider-style-dashed\" style=\"margin:30px 0;border-width:8px;border-color:#009900\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-lightbulb-exclamation\"><\/i> Notes<\/h3>\n\n\n\n<ol class=\"wp-block-list\"><li>A timeout may occur depending on the number of records or aggregate labels.<\/li><li>Aggregate labels are sorted in ascending or descending order.<\/li><li>Numeric parsing depends on JavaScript <a rel=\"noreferrer noopener\" aria-label=\"parseFloat() (opens in a new tab)\" href=\"https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/JavaScript\/Reference\/Global_Objects\/parseFloat\" target=\"_blank\">parseFloat()<\/a>.<\/li><li>If a prefix precedes a number, it will not be recognized as a number (e.g. &#8220;$100&#8221;).<\/li><li>The period &#8220;.&#8221; is recognized as a decimal point.<\/li><li>Assuming a digit separator, all commas will be removed before determining the numeric value.<\/li><li>(2019-12-06) If non-numeric data is mixed in the specified column, the record is regarded as zero and not added.<\/li><\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-images\"><\/i> Capture<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/07\/tsv-string-create-cross-tab-en.png?ssl=1\" alt=\"\" class=\"wp-image\" style=\"border:10px solid #aaaaaa; padding:5px; margin:5px;\"><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?ssl=1\" target=\"_blank\" rel=\"noreferrer noopener\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"675\" data-attachment-id=\"82432\" data-permalink=\"https:\/\/support.questetra.com\/en\/addons\/tsv-string-create-cross-tab-for-numerical-sum\/attachment\/tsv-string-create-cross-tab-for-numerical-sum-capture-en\/\" data-orig-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?fit=1200%2C675&amp;ssl=1\" data-orig-size=\"1200,675\" data-comments-opened=\"0\" data-image-title=\"TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?fit=1024%2C576&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?resize=1200%2C675&#038;ssl=1\" alt=\"Creates a crosstab (pivot table) for numeric fields in TSV data. For example, the &quot;Amount&quot; in the order history (TSV) is aggregated on two axes, &quot;Responsible&quot; and &quot;Product&quot;. The fields for X-axis and Y-axis aggregation are specified by TSV field ID.\" class=\"wp-image-82432\" srcset=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?w=1200&amp;ssl=1 1200w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?resize=560%2C315&amp;ssl=1 560w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?resize=1024%2C576&amp;ssl=1 1024w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-capture-en.png?resize=768%2C432&amp;ssl=1 768w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-balance-scale\"><\/i> See also<\/h3>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-questetra-support wp-block-embed-questetra-support\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"WMRNBDxWXK\"><a href=\"https:\/\/support.questetra.com\/en\/addons\/tsv-string-sort-by-numeric-2021\/\">TSV String, Sort by Numeric<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;TSV String, Sort by Numeric&#8221; &#8212; Questetra Support\" src=\"https:\/\/support.questetra.com\/addons\/tsv-string-sort-by-numeric-2021\/embed\/#?secret=vXvIa1vtIE#?secret=WMRNBDxWXK\" data-secret=\"WMRNBDxWXK\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Creates a crosstab table (or pivot table) for the numeric fields of TSV data. For example, the &#8220;Amount&#8221; in the order history (TSV) is aggregated on the two axes of &#8220;Person Responsible&#8221; and &#8220;Product&#8221;. The fields for X-axis and Y-axis aggregation are specified by the TSV field ID.<\/p>\n","protected":false},"author":3,"featured_media":82430,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","_uag_custom_page_level_css":"","advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_wpcom_ai_launchpad_first_post":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"{title}\n\n{excerpt}\n\n{url}","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_wpas_customize_per_network":false,"jetpack_post_was_ever_published":false},"categories":[168],"tags":[389],"faq_section":[],"class_list":["post-109999","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-addons","tag-tsv-csv"],"jetpack_publicize_connections":[],"uagb_featured_image_src":{"full":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1",1200,675,false],"thumbnail":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=440%2C440&ssl=1",440,440,true],"medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=560%2C315&ssl=1",560,315,true],"medium_large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=768%2C432&ssl=1",768,432,true],"large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1024%2C576&ssl=1",1024,576,true],"1536x1536":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1",1200,675,true],"2048x2048":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-landscape-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-portrait-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=900%2C675&ssl=1",900,675,true],"newspack-article-block-square-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-landscape-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=800%2C600&ssl=1",800,600,true],"newspack-article-block-portrait-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=600%2C675&ssl=1",600,675,true],"newspack-article-block-square-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=800%2C675&ssl=1",800,675,true],"newspack-article-block-landscape-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=600%2C450&ssl=1",600,450,true],"newspack-article-block-portrait-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=450%2C600&ssl=1",450,600,true],"newspack-article-block-square-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=600%2C600&ssl=1",600,600,true],"newspack-article-block-landscape-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=400%2C300&ssl=1",400,300,true],"newspack-article-block-portrait-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=300%2C400&ssl=1",300,400,true],"newspack-article-block-square-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=400%2C400&ssl=1",400,400,true],"newspack-article-block-landscape-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=200%2C150&ssl=1",200,150,true],"newspack-article-block-portrait-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=150%2C200&ssl=1",150,200,true],"newspack-article-block-square-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?resize=200%2C200&ssl=1",200,200,true],"newspack-article-block-uncropped":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1",1200,675,true]},"uagb_author_info":{"display_name":"Hirotaka NISHI","author_link":"https:\/\/support.questetra.com\/en\/author\/nishiquestetra\/"},"uagb_comment_info":3,"uagb_excerpt":"Creates a crosstab table (or pivot table) for the numeric fields of TSV data. For example, the \"Amount\" in the order history (TSV) is aggregated on the two axes of \"Person Responsible\" and \"Product\". The fields for X-axis and Y-axis aggregation are specified by the TSV field ID.","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9DiIh-sCb","jetpack-related-posts":[{"id":82429,"url":"https:\/\/support.questetra.com\/en\/addons\/tsv-string-create-cross-tab-for-numerical-sum\/","url_meta":{"origin":109999,"position":0},"title":"TSV String, Create Cross Tab for Numerical Sum","author":"IMAMURA, Genichi","date":"2019-12-05","format":false,"excerpt":"Creates a crosstab (pivot table) for numeric fields in TSV data. For example, the \"Amount\" in the order history (TSV) is aggregated on two axes, \"Responsible\" and \"Product\". The fields for X-axis and Y-axis aggregation are specified by TSV field ID.","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"TSV String, Create Cross Tab for Numerical Sum","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":82436,"url":"https:\/\/support.questetra.com\/ja\/addons\/tsv-string-create-cross-tab-for-numerical-sum\/","url_meta":{"origin":109999,"position":1},"title":"TSV\u6587\u5b57\u5217, \u6570\u5024\u5408\u8a08\u306e\u30af\u30ed\u30b9\u96c6\u8a08","author":"IMAMURA, Genichi","date":"2019-12-05","format":false,"excerpt":"TSV\u30c7\u30fc\u30bf\u306e\u6570\u5024\u30d5\u30a3\u30fc\u30eb\u30c9\u306b\u3064\u3044\u3066\u3001\u30af\u30ed\u30b9\u96c6\u8a08\u8868\uff08\u30d4\u30dc\u30c3\u30c8\u30c6\u30fc\u30d6\u30eb\uff09\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002\u305f\u3068\u3048\u3070\u53d7\u6ce8\u5c65\u6b74\uff08TSV\uff09\u306e \"\u91d1\u984d\" \u306b\u3064\u3044\u3066 \"\u62c5\u5f53\u8005\" \u3068 \"\u88fd\u54c1\" \u306e2\u8ef8\u3067\u96c6\u8a08\u3057\u307e\u3059\u3002\u306a\u304a\u3001X\u8ef8\u96c6\u7d04\u304a\u3088\u3073Y\u8ef8\u96c6\u7d04\u3067\u4f7f\u7528\u3055\u308c\u308b\u30d5\u30a3\u30fc\u30eb\u30c9\u306e\u6307\u5b9a\u306b\u306f\u3001TSV\u30d5\u30a3\u30fc\u30eb\u30c9ID\u3067\u8a2d\u5b9a\u3057\u307e\u3059\u3002","rel":"","context":"In &quot;\u30a2\u30c9\u30aa\u30f3&quot;","block_context":{"text":"\u30a2\u30c9\u30aa\u30f3","link":"https:\/\/support.questetra.com\/ja\/category\/addons\/"},"img":{"alt_text":"TSV\u6587\u5b57\u5217, \u6570\u5024\u5408\u8a08\u306e\u30af\u30ed\u30b9\u96c6\u8a08","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-ja.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-ja.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-ja.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-ja.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-ja.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":78314,"url":"https:\/\/support.questetra.com\/en\/addons\/tsv-string-sum-of-number-column\/","url_meta":{"origin":109999,"position":2},"title":"TSV String; Sum of Number Column","author":"IMAMURA, Genichi","date":"2019-08-05","format":false,"excerpt":"Calculates the sum of values in numeric column. If non-numeric data is mixed in the specified column, the record is regarded as zero and not added.","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/08\/TSV-String-Sum-of-Number-Column-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":84857,"url":"https:\/\/support.questetra.com\/en\/addons\/converter-tsv-string-to-table\/","url_meta":{"origin":109999,"position":3},"title":"Converter: TSV-String to Table","author":"IMAMURA, Genichi","date":"2020-02-28","format":false,"excerpt":"Converts TSV String to Table-type data. The string value of all cells in the TSV string is copied over Table-type data B. If there is an inconsistency in the Table column design, an error may occur.","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"Converter: TSV-String to Table","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/02\/Converter-TSV-String-to-Table-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/02\/Converter-TSV-String-to-Table-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/02\/Converter-TSV-String-to-Table-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/02\/Converter-TSV-String-to-Table-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/02\/Converter-TSV-String-to-Table-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":89039,"url":"https:\/\/support.questetra.com\/en\/addons\/master-table-create-by-tsv-string\/","url_meta":{"origin":109999,"position":4},"title":"Master Table, Create by TSV String","author":"IMAMURA, Genichi","date":"2020-06-15","format":false,"excerpt":"Creates a new master file (OptionsXML) by referring to TSV String. The specified column data are set as Master IDs, and the others are set as Master Labels. For automation, place [Service Task (Choices Update)] downstream.","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"Master Table, Create by TSV String","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/Master-Table-Create-by-TSV-String-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/Master-Table-Create-by-TSV-String-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/Master-Table-Create-by-TSV-String-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/Master-Table-Create-by-TSV-String-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/06\/Master-Table-Create-by-TSV-String-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":70808,"url":"https:\/\/support.questetra.com\/en\/addons\/journaltsv-generator\/","url_meta":{"origin":109999,"position":5},"title":"Journal Slip TSV from Invoice Data","author":"Hirotaka NISHI","date":"2018-11-14","format":false,"excerpt":"Generate transfer slip TSV (tab-separated-values) with sales amount, sales date, due date. It supports cases where add a sales collectively or divided into several months or mixture of those. In order to generate a file for the accounting system, you need to place a converter such as \"TSV to Excel-CSV\u2026","rel":"","context":"In &quot;Add-ons&quot;","block_context":{"text":"Add-ons","link":"https:\/\/support.questetra.com\/en\/category\/addons\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2018\/11\/journal-tsv-generator-header.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2018\/11\/journal-tsv-generator-header.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2018\/11\/journal-tsv-generator-header.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2018\/11\/journal-tsv-generator-header.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2018\/11\/journal-tsv-generator-header.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]}],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/12\/TSV-String-Create-Cross-Tab-for-Numerical-Sum-en.png?fit=1200%2C675&ssl=1","_links":{"self":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/109999","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/comments?post=109999"}],"version-history":[{"count":7,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/109999\/revisions"}],"predecessor-version":[{"id":114694,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/109999\/revisions\/114694"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/media\/82430"}],"wp:attachment":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/media?parent=109999"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/categories?post=109999"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/tags?post=109999"},{"taxonomy":"faq_section","embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/faq_section?post=109999"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}