Built-in automatic processing step [Update CSV Data] can also be used to perform the same processing. (Recommended)
The settings for [Update CSV Data] when extracting rows where cells match by comparing specific columns in two TSV strings are as follows:
C1: Data field for entering the source TSV string
C1a: “TSV” / “No header (data starts from row 1)”
C1b: Table name for calling in C3 (e.g., TSV string A)
C2: Data field for entering the target TSV string to compare against
C2a: “TSV” / “No header (data starts from row 1)”
C2b: Table name to be called in C3 (e.g., TSV String B)
C3: Specify the query below (Replace “TSV string A”, “TSV string B”, “col2”, and “col3” with actual table and column names.)
-- Extract rows from TSV string B matching the conditions below
SELECT t2.*
FROM "TSV string B" AS t2
-- Rows where the 3rd column (col3) of TSV string A matches the 2nd column (col2) of TSV string B
WHERE t2."col2" IN (SELECT "col3" FROM "TSV string A");
C4: “Save the entire table to a single data item in CSV / TSV format”
C4a: Data item storing the resulting TSV string
C4b: “TSV” / “No header (data starts from row 1)”
C4c: “Only when necessary”
Two Tsv Strings, Extract Cell-Matched Lines
Compares a specific column of TSV string A with a specific column of B and extracts the rows of B that have a matching cell in A. If A and B match exactly, all lines in B are returned. If they are completely different, an empty string is 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.
// To extract the customers with whom they have on-going transactions in list A.
// Also for extracting sales records that are registered in product master A.
// Line sorting is NOT required.
//
// Notes(ja):
// セルを比較し、行を抽出します。
// 前月取引先一覧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 = "";
for( let i = 0; i < arrStrB.length; i++ ){
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 + "]" );
strOutput += arrStrB[i] + "\n";
break;
}
}
}
strOutput = strOutput.replace(/[\n]*$/, "");
//// == Data Updating / ワークフローデータへの代入 ==
if( strOutput === "" ){
engine.log( " AutomatedTask DataUpdating: Output Text empty" );
}
engine.setDataByNumber( dataIdC, strOutput );
} //////// END "main()" /////////////////////////////////////////////////////////////////
