Two Tsv Strings, Extract Cell-Mismatched Lines
Two Tsv Strings, Extract Cell-Mismatched Lines
Compares a specific column of TSV string A with a specific column of B and extracts the rows of B that do not have a cell match for A. If A and B match exactly, an empty string is returned. If they are completely different, all lines in B are returned.
Configs
  • A: Set TSV String *#{EL}
  • A-id: Set ID of column to compare *#{EL}
  • B: Set TSV String *#{EL}
  • B-id: Set ID of column to compare *#{EL}
  • C: Select STRING DATA for Extracted TSV String (update) *
Script (click to open)

// GraalJS Script (engine type: 2)
// 
// Notes:
// Compares cells and extracts lines.
// Only the inserted lines and cell-edited lines are extracted.
//  If you want to extract the deleted lines, replace the AB settings.
// To extract new business partners who are not in partner list A last month.
// Also for extracting sales records that are not registered in product master A.
// Line sorting is NOT required.
// 
// Notes(ja):
// セルを比較し、行を抽出します。
// 新規挿入された行やセル編集された行が抽出されます。
//  削除された行を抽出したい場合は、AB設定を入れ換えてください。
// 前月取引先一覧Aにない新規取引先を抽出するといったケースに利用できます。
// 商品マスタAに未登録な商品売上レコードを抽出するといったケースにも利用できます。
// 行ソートされている必要はありません。


//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strA    = configs.get( "conf_StrA" ) + "";     // config required
const idA     = configs.get( "conf_IdA" ) + "";      // config required
const strB    = configs.get( "conf_StrB" ) + "";     // config required
const idB     = configs.get( "conf_IdB" ) + "";      // config required
const dataIdC = configs.get( "conf_DataIdC" ) + "";  // config required

if( strA === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Str A} is empty \n" );
}
if( strB === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Str B} is empty \n" );
}

if( idA === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {ID A} is empty \n" );
}
if( idB === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {ID B} is empty \n" );
}
const zeroPositiveInt = /^([1-9]\d*|0)$/; // RegExp
let numIdA = -1;
if( zeroPositiveInt.test( idA ) ){
  numIdA = parseInt(idA, 10);
}else{
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config Number {ID A} must be a positive integer or zero \n" );
}
let numIdB = -1;
if( zeroPositiveInt.test( idB ) ){
  numIdB = parseInt(idB, 10);
}else{
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config Number {ID B} must be a positive integer or zero \n" );
}


//// == Data Retrieving / ワークフローデータの参照 ==
// Nothing (except EL-expression Config)


//// == Calculating / 演算 ==
let twoDimArrayA = []; // for TSV-A
const arrStrA = strA.split("\n");
for( let i = 0; i < arrStrA.length; i++ ){
  twoDimArrayA[i] = arrStrA[i].split("\t");
}
let twoDimArrayB = []; // for TSV-B
const arrStrB = strB.split("\n");
for( let i = 0; i < arrStrB.length; i++ ){
  twoDimArrayB[i] = arrStrB[i].split("\t");
}
engine.log( " AutomatedTask MultilineString:" + 
            " TSV {A}, number of lines " + arrStrA.length );
engine.log( " AutomatedTask MultilineString:" + 
            " TSV {B}, number of lines " + arrStrB.length );

let strOutput = "";
let matchFlag = 0;
for( let i = 0; i < arrStrB.length; i++ ){
  matchFlag = 0;
  for( let j = 0; j < arrStrA.length; j++ ){
    if( twoDimArrayB[i][numIdB] === twoDimArrayA[j][numIdA] ){
      engine.log( " AutomatedTask Match:" +
                  " B[" + i + "][" + numIdB + "] ==" +
                  " A[" + j + "][" + numIdA + "]" );
      matchFlag = 1;
      break;
    }
  }
  if( matchFlag === 0 ){
    strOutput += arrStrB[i] + "\n";
  }
}
strOutput = strOutput.replace(/[\n]*$/, "");


//// == Data Updating / ワークフローデータへの代入 ==
if( strOutput === "" ){
  engine.log( " AutomatedTask DataUpdating: Output Text empty" );
}
engine.setDataByNumber( dataIdC, strOutput );

} //////// END "main()" /////////////////////////////////////////////////////////////////

Download

2021-07-29 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/two-tsv-strings-extract-cell-mismatched-lines-2021/
The Add-on import feature is available with Professional or Enterprise edition.

Notes

  1. Compares cells and extracts lines.
  2. Only the inserted lines and cell-edited lines are extracted.
    1. If you want to extract the deleted lines, replace the AB settings.
  3. This can be used in cases where you want to extract new clients that are not in the previous month’s client list A.
  4. You can also use this function to extract product sales records that are not yet registered in the product master A.
  5. Line sorting is NOT required.

Capture

Extracts B lines that DO NOT exist in A-Cell with comparing the specific column of TSV-A and the specific column of TSV-B. If A and B match exactly, an empty string is returned. If they are completely different, all lines in B are returned.

See also

1 thought on “Two Tsv Strings, Extract Cell-Mismatched Lines”

  1. Pingback: Two Tsv Strings, Extract Cell-Mismatched Lines – Questetra Support

Comments are closed.

%d bloggers like this: