Is there any api to find uncommon values present in two infotables.

[AP_10343008]

Is there any api to find uncommon values present in two infotables. 

[Catalina]

Hi @AP_10343008,

Thank you for your question! 

I’d like to recommend to bring more details and context to your initial inquiry. 

It also helps to have screenshot(s) to better understand what you are trying to do in your process. 

Please refer to this guideline to make your questions more likely to receive a quick and useful answer. 

This will increase your chances to receive meaningful help from other Community members. 

Furthermore, please consult this Community Search guideline as well, which boosts up your chances of finding a resolution to your topic(s) much faster. 

Thank you for your participation and please let me know if you need further assistance! 

Best regards,

[AP_10343008]

I have two infotables. Iam using Intersect API from infotablefunctions to get matched rows present in 2 tables. 

I want to know is there any api present to return unmatched rows in both infotables.?

[Rocko]

Do you mean an anti-join? Please give an example.

[AP_10343008]

Yes correct. Iam looking for api which returns rows from one table for which there are no matching records in another table.

[Rocko]

There's nothing ready-made, you'll have to do it yourself. Here's one way:

1) Antijoin of two sets A and B means union of A minus B with B minus A.

2) this is an example of A minus B, let's say we want to antijoin on column id

// example setup, create two infotables
let result = Resources["InfoTableFunctions"].CreateInfoTable();
result.AddField({name:"id", baseType:"STRING"});
result.AddField({name:"name", baseType:"STRING"});
result.AddField({name:"age", baseType:"INTEGER"});

let deleteTable = Resources["InfoTableFunctions"].Clone({t1:result});
let resultTable = Resources["InfoTableFunctions"].Clone({t1:result});

result.AddRow({id:"4",name:"A",age:10});
result.AddRow({id:"5",name:"B",age:20});
result.AddRow({id:"6",name:"C",age:30});
result.AddRow({id:"7",name:"D",age:40});

deleteTable.AddRow({id:"6",name:"C",age:30});
deleteTable.AddRow({id:"7",name:"D",age:40});
deleteTable.AddRow({id:"8",name:"E",age:50});
// now we have setup sample data

// "join" column is "id"
let delArray=deleteTable.rows.toArray().map(r=>r.id);
result.rows.toArray().forEach(row=>{ if (!delArray.includes(row.id)) resultTable.AddRow(row);});

result=resultTable;

3) Move this into a service, call it with (A,B), then (B,A) then join the results using Resources["InfoTableFunctions"].Union

[Rocko]

This code does it all in one go, it's a bit more efficient.

// setting up two infotable with the same rows
let tableA = Resources["InfoTableFunctions"].CreateInfoTable();
tableA.AddField({name:"id", baseType:"STRING"});
tableA.AddField({name:"name", baseType:"STRING"});
tableA.AddField({name:"age", baseType:"INTEGER"});

let tableB = Resources["InfoTableFunctions"].Clone({t1:tableA});
let resultTable = Resources["InfoTableFunctions"].Clone({t1:tableA});

tableA.AddRow({id:"4",name:"A",age:10});
tableA.AddRow({id:"5",name:"B",age:20});
tableA.AddRow({id:"6",name:"C",age:30});
tableA.AddRow({id:"7",name:"D",age:40});

tableB.AddRow({id:"6",name:"C",age:30});
tableB.AddRow({id:"7",name:"D",age:40});
tableB.AddRow({id:"8",name:"E",age:50});

// run the antijoin
let delArray=tableB.rows.toArray().map(r=>r.id);
let injoin=[];
tableA.rows.toArray().forEach(row=>{ if (!delArray.includes(row.id)) resultTable.AddRow(row); else injoin.push(row.id)});
tableB.rows.toArray().forEach(row=>{ if (!injoin.includes(row.id)) resultTable.AddRow(row)});

result=resultTable;