Skip to main content
Best answer by Rocko

Here's a significant improvement to the previous code. What is slow in my previous code is the "includes" part which does not perform well. What is fast is when you check the existence of indexes in an array instead of the value. But with your complex keys, we can't do that because the keys are not plain numbers. The alternative to array checking is to check for the existence of properties in an object. So we convert each key into an objects property and check against that. With this, the task can be done in few seconds, even with 2x200.000 items. Code goes like this:

 } else if (PartsFromMasterBOM.length > 0 && PartsFromModelBOM.length > 0) {
 //when ComparisonChanged==true

 // create new empty infotables for result:
 let newPartsFromModelBOM=DataShapes["PUT_HERE_CORRECT_DATASHAPE"].CreateValues();
 let newPartsFromMasterBOM=DataShapes["PUT_HERE_CORRECT_DATASHAPE"].CreateValues();
		let delMasterObject={};
		let delModelObject={};
		PartsFromMasterBOM.rows.toArray().filter(r=>r.ObjectType!="Modular Part")
		.forEach(r=>delMasterObject["Pt"+(r.PARTNUMBER+"_"+(IncludeEnggSeqForComparison?r.ENGINEERINGSEQUENCE:"")+"_"+(IncludeQtyForComparison?r.QUANTITY:"")+"_"+(IncludeParentForComparison?r.PARENTITEMNUMBER:""))
								 ]=true);
				
		PartsFromModelBOM.rows.toArray().forEach(r=>{ 
			let code="Pt"+r.PARTNUMBER+"_"+(IncludeEnggSeqForComparison?r.ENGINEERINGSEQUENCE:"")+"_"+(IncludeQtyForComparison?r.QUANTITY:"") +"_"+(IncludeParentForComparison?r.PARENTITEMNUMBER:"");
			if (!delMasterObject[code]) 
				newPartsFromModelBOM.AddRow(r);
			else 
				delModelObject[code]=true; 
		});

		PartsFromMasterBOM.rows.toArray().forEach(r=>{ 
			if (!delModelObject["Pt"+r.PARTNUMBER+"_"+(IncludeEnggSeqForComparison?r.ENGINEERINGSEQUENCE:"")+"_"+(IncludeQtyForComparison?r.QUANTITY:"")+"_"+(IncludeParentForComparison?r.PARENTITEMNUMBER:"")]) 
				newPartsFromMasterBOM.AddRow(r);});
 
 result.AddRow({ UnCommonPartsInMasterBOM: newPartsFromModelBOM, UnCommonPartsInModelBOM: newPartsFromMasterBOM });
 }

 

3 replies

Catalina
CatalinaCommunity Manager
Community Moderator
September 24, 2024

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,

Catalina | PTC Community Moderator
A
AP_1034300816-PearlAuthor
16-Pearl
September 24, 2024

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
Rocko19-Tanzanite
19-Tanzanite
September 26, 2024

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

A
AP_1034300816-PearlAuthor
16-Pearl
September 26, 2024

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

Rocko
Rocko19-Tanzanite
19-Tanzanite
September 26, 2024

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

 

Terms & ConditionsCookie settingsAccessibility statement