{"id":140884,"date":"2023-01-11T11:02:43","date_gmt":"2023-01-11T02:02:43","guid":{"rendered":"https:\/\/support.questetra.com\/?p=140884"},"modified":"2023-01-11T11:02:45","modified_gmt":"2023-01-11T02:02:45","slug":"tsv-string-remove-column-duplicate-lines-2023","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/en\/addons\/tsv-string-remove-column-duplicate-lines-2023\/","title":{"rendered":"TSV String, Remove Column-Duplicate Lines"},"content":{"rendered":"\n

TSV String, Remove Column-Duplicate Lines<\/strong><\/h4>\n\n\n\n

TSV \u6587\u5b57\u5217, \u30ab\u30e9\u30e0\u91cd\u8907\u884c\u306e\u524a\u9664<\/a><\/p>\n\n\n\n

Extracts and deletes rows with duplicate column elements. In forward mode it keeps the first row, in reverse mode it keeps the last row. Note that “ABC” and “Abc” are recognized as different columns. (case sensitive)<\/p>\n\n\n\n

\"Auto<\/figure>\n\n\n\n
\n
Configs for this Auto Step<\/summary>
\n
\n
StrConfA1<\/dt>
A1: Set Input TSV text *<\/span>#{EL}<\/sup><\/span><\/dd>\n
BoolConfB1<\/dt>
B1: Forward mode or Reverse mode<\/dd>\n
StrConfB2<\/dt>
B2: Set Column IDs to be Evaluated (eg. “0,3”) *<\/span>#{EL}<\/sup><\/span><\/dd>\n
SelectConfC1<\/dt>
C1: Select STRING DATA for Output TSV Text (update)<\/dd>\n
SelectConfC2<\/dt>
C2: Select STRING DATA for Removed Lines (update)<\/dd>\n\n<\/dl>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
\n
Script (click to open)<\/summary>
\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 strInput         = configs.get      ( "StrConfA1" );    \/\/\/ REQUIRED\n  if( strInput       === "" ){\n    throw new Error( "\\n AutomatedTask ConfigError:" +\n                     " Config {A1: String} is empty \\n" );\n  }\nconst boolReverseMode  = configs.getObject( "BoolConfB1" );   \/\/\/ TOGGLE\n  \/\/ https:\/\/questetra.zendesk.com\/hc\/en-us\/articles\/360024574471-R2300 "Boolean object"\n  if( boolReverseMode ){ \/\/ true\n    engine.log( " AutomatedTask Config: Reverse order Search");\n  }else{\n    engine.log( " AutomatedTask Config: Forward order Search");\n  }\nconst strKeyColIds     = configs.get      ( "StrConfB2" );    \/\/\/ REQUIRED\n  if( strKeyColIds   === "" ){\n    throw new Error( "\\n AutomatedTask ConfigError:" +\n                     " Config {B2: KeyColIds} is empty \\n" );\n  }\n  const regNonNegativeIntCsv = \/^[0-9,]*$\/; \/\/ RegExp\n  if( ! regNonNegativeIntCsv.test( strKeyColIds ) ){\n    throw new Error( "\\n AutomatedTask ConfigError:" +\n                     " Config {B2: KeyColIds} allows only id numbers and commas \\n" );\n  }\n\nconst strPocketOutput  = configs.getObject( "SelectConfC1" ); \/\/ NotRequired\nconst strPocketRemoved = configs.getObject( "SelectConfC2" ); \/\/ 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 ==\nconst arr2dInput   = parseAsRectangular( strInput ); \/\/ creates a 2D array.\nconst arrKeyColIds = strKeyColIds.split( "," );\n\nlet   arrOutput    = [];\nlet   arrRemoved   = [];\nlet   setUniqKeys  = new Set();\n\/\/ The Set object lets you store unique values of any type. Require ES2015(ES6)\n\/\/ https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/JavaScript\/Reference\/Global_Objects\/Set\n\/\/ https:\/\/developer.mozilla.org\/ja\/docs\/Web\/JavaScript\/Reference\/Global_Objects\/Set\n\nif ( boolReverseMode ) {\n  arr2dInput.reverse(); \/\/ [ "line0", "line1", "line2" ] \u21d2 [ "line2", "line1", "line0" ]\n}\nfor ( let i = 0; i < arr2dInput.length; i++ ) {\n  let strKey = "";\n  for ( let j = 0; j < arrKeyColIds.length; j++ ){ \/\/ e.g.; "CellA\\tCellB\\tCellC"\n    strKey  += arr2dInput[i][ arrKeyColIds[j] ];\n    if ( j !== arrKeyColIds.length - 1 ){\n      strKey += "\\t";\n    }\n  }\n  \/\/ engine.log( " AutomatedTask StrKey: " + strKey );\n  if ( setUniqKeys.has ( strKey ) ) {\n    \/\/ engine.log( " AutomatedTask SetUniqKeys has: " + strKey + " (line: " + i + ")" );\n    arrRemoved.push ( arr2dInput[i].join( '\\t' ) );\n  } else {\n    arrOutput.push  ( arr2dInput[i].join( '\\t' ) );\n    setUniqKeys.add ( strKey );\n  }\n}\nif ( boolReverseMode ) {\n  if ( arrOutput.length !== 0 ){\n       arrOutput.reverse(); \/\/ [ "line2", "line1", "line0" ] \u21d2 [ "line0", "line1", "line2" ]\n  }\n  if ( arrRemoved.length !== 0 ){\n       arrRemoved.reverse();\n  }\n}\n\nlet strOutput  = arrOutput?.join( '\\n' ) ?? "";\nlet strRemoved = arrRemoved?.join( '\\n' ) ?? "";\n\/\/ https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/JavaScript\/Reference\/Operators\/Optional_chaining\n\n\n\/\/\/\/ == Data Updating \/ \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 ==\nif ( strPocketOutput !== null ) {\n  engine.setData ( strPocketOutput,  strOutput );\n}\nif ( strPocketRemoved !== null ) {\n  engine.setData ( strPocketRemoved, strRemoved );\n}\n\n} \/\/\/\/\/\/\/\/ END "main()" \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\n\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\n\/*\nNotes:\n- When the process reaches the automated step, the "TSV text" will be automatically evaluated.\n    - All rows with duplicate specified columns (primary keys) are deleted.\n    - In Forward mode, the first occurrence of a row is retained.\n    - And the second and subsequent occurrences are deleted.\n- To check for duplicates in the so-called "composite primary key," specify multiple column IDs.\n    - Setting e.g.; "0,1,2", "0,2"\nAppendix-en:\n- If the "Output TSV Text" is empty, the string type data item is updated with empty characters.\n- If the "delete line" is empty, the string type data item is updated with an empty string.\n- In computer science, a set is an abstract data type:\n    - that can store unique values, without any particular order.\n    - It is a computer implementation of the mathematical concept of a finite set.\n    - https:\/\/en.wikipedia.org\/wiki\/Set_(abstract_data_type)\n    - https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/JavaScript\/Reference\/Global_Objects\/Set\n\nNotes-ja:\n- \u6848\u4ef6\u304c\u81ea\u52d5\u5de5\u7a0b\u306b\u5230\u9054\u3057\u305f\u969b\u3001\u300cTSV\u30c6\u30ad\u30b9\u30c8\u300d\u304c\u8a55\u4fa1\u3055\u308c\u307e\u3059\u3002\n    - \u6307\u5b9a\u30ab\u30e9\u30e0\uff08\u4e3b\u30ad\u30fc\uff09\u304c\u91cd\u8907\u3059\u308b\u884c\u304c\u5168\u3066\u524a\u9664\u3055\u308c\u307e\u3059\u3002\n    - \u6b63\u9806\u30e2\u30fc\u30c9\uff08Forward mode\uff09\u306e\u5834\u5408\u3001\u6700\u521d\u306b\u51fa\u73fe\u3057\u305f\u884c\u304c\u4fdd\u6301\u3055\u308c\u30012\u56de\u76ee\u4ee5\u964d\u306b\u51fa\u73fe\u3059\u308b\u884c\u306f\u524a\u9664\u3055\u308c\u307e\u3059\u3002\n- \u3044\u308f\u3086\u308b\u300c\u8907\u5408\u4e3b\u30ad\u30fc\u300d\u3067\u91cd\u8907\u3092\u78ba\u8a8d\u3057\u305f\u3044\u5834\u5408\u3001\u30ab\u30e9\u30e0ID\u3092\u8907\u6570\u6307\u5b9a\u3057\u307e\u3059\u3002\n    - \u8a2d\u5b9a\u4f8b\uff1a "0,1,2"\u3001"0,2"\nAppendix-ja:\n- \u3082\u3057\u300c\u51fa\u529bTSV\u30c6\u30ad\u30b9\u30c8\u300d\u304c\u7a7a\u306e\u5834\u5408\u3001\u6587\u5b57\u5217\u578b\u30c7\u30fc\u30bf\u9805\u76ee\u306f\u3001\u7a7a\u6587\u5b57\u3067\u66f4\u65b0\u3055\u308c\u307e\u3059\u3002\n- \u3082\u3057\u300c\u524a\u9664\u884c\u300d\u304c\u7a7a\u306e\u5834\u5408\u3001\u6587\u5b57\u5217\u578b\u30c7\u30fc\u30bf\u9805\u76ee\u306f\u3001\u7a7a\u6587\u5b57\u3067\u66f4\u65b0\u3055\u308c\u307e\u3059\u3002\n- Set \u3068\u306f\u3001\u30b3\u30f3\u30d4\u30e5\u30fc\u30bf\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u3067\u7528\u3044\u3089\u308c\u308b\u62bd\u8c61\u30c7\u30fc\u30bf\u578b\u306e\u4e00\u7a2e\u3002\n    - \u9806\u5e8f\u306e\u306a\u3044\u30c7\u30fc\u30bf\u306e\u96c6\u307e\u308a\uff08\u6709\u9650\u96c6\u5408\uff09\u3092\u8868\u73fe\u3059\u308b\u62bd\u8c61\u30c7\u30fc\u30bf\u578b\u3002\n    - \u540c\u4e00\u306e\u30c7\u30fc\u30bf\u306f\u4e00\u3064\u3057\u304b\u542b\u307e\u308c\u306a\u3044\u3053\u3068\u304c\u4fdd\u8a3c\u3055\u308c\u308b\u3002\n    - https:\/\/w.wiki\/6DCr\n    - https:\/\/developer.mozilla.org\/ja\/docs\/Web\/JavaScript\/Reference\/Global_Objects\/Set\n*\/\n\n<\/code><\/pre><\/div>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n
Download<\/span><\/h3>\n\n\n\n