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

[AP_10343008]

Yes I tried many codes like this. But server hanged when the each table having minimum 15k rows. 

Now I changed the logic to compare every 250 rows in chunks instead of comparing all rows at same time. Now users are not facing performance issue. If the issue occur then ill reduce the count of rows to be compared in single loop.

As I didnt get OOTB API to get uncommon rows, 

This solution solves the performance issue while comparing larger number of data.

[Rocko]

Ok, I just ran this code locally with two infotables with 20k rows each and it took between 1 and 2 minutes, depending on how many rows were returned. If your server hangs, either your ScriptTimeout is too low or your server has a problem.

[Rocko]

And of course, if your tables use an integer field for the id, you can make it all run in one or two seconds without chunking (antijoin 2x20k rows, it takes 7 secs with 2x200k rows) by using the index of the arrays instead of their content. I would post the code but already marked this as solved. so this is just for information.

[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;

[AP_10343008]

If you able to run the code in 2mins of 20k rows each, then I'll try your logic to reduce execution time.

Actual Requirement is:

Matched rows should be removed in both Table1 and Table2. Unmatched rows in Table1 output will be passed to Grid1 in Mashup and Unmatched rows in Table2 output will be passed to Grid2 in mashup. 

I have attached the current logic I used in service. Kindly check and let me know. 

[Rocko]

So you have multiple, configurable fields to compare on, which makes it a bit more complex. You pay for this configurability with speed, but this is how UnCommonPartsInMasterBOM could look like in the else part (l67 or so). I couldn't run it since I lack the datashapes and data, but you get the idea. You create an array with a composite key and check against the array.  

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

        let delModelArray=PartsFromModelBOM.rows.toArray()
            .filter(r=>r.ObjectType!="Modular Part")
            .map(r=>""+(r.PARTNUMBER
                        +"_"+(IncludeEnggSeqForComparison?r.ENGINEERINGSEQUENCE:"")
                        +"_"+(IncludeQtyForComparison?r.QUANTITY:"")
                        +"_"+(IncludeParentForComparison?r.PARENTITEMNUMBER:"")
            ));

        let delMasterArray=PartsFromMasterBOM.rows.toArray()
            .filter(r=>r.ObjectType!="Modular Part")
            .map(r=>""+(r.PARTNUMBER
                        +"_"+(IncludeEnggSeqForComparison?r.ENGINEERINGSEQUENCE:"")
                        +"_"+(IncludeQtyForComparison?r.QUANTITY:"")
                        +"_"+(IncludeParentForComparison?r.PARENTITEMNUMBER:"")
            ));


        // create new empty infotables for result:
        let newPartsFromModelBOM=DataShapes["PUT_HERE_CORRECT_DATASHAPE"].CreateValues();
        let newPartsFromMasterBOM=DataShapes["PUT_HERE_CORRECT_DATASHAPE"].CreateValues();
        
        PartsFromModelBOM.rows.toArray().forEach(r=>{ 
			if (!delMasterArray.includes(r.PARTNUMBER
											+"_"+(IncludeEnggSeqForComparison?r.ENGINEERINGSEQUENCE:"")
											+"_"+(IncludeQtyForComparison?r.QUANTITY:"")
											+"_"+(IncludeParentForComparison?r.PARENTITEMNUMBER:""))) 
				newPartsFromModelBOM.AddRow(r);});
        PartsFromMasterBOM.rows.toArray().forEach(r=>{ 
			if (!delModelArray.includes(r.PARTNUMBER
											+"_"+(IncludeEnggSeqForComparison?r.ENGINEERINGSEQUENCE:"")
											+"_"+(IncludeQtyForComparison?r.QUANTITY:"")
											+"_"+(IncludeParentForComparison?r.PARENTITEMNUMBER:""))) 
				newPartsFromMasterBOM.AddRow(r);});

        result.AddRow({ UnCommonPartsInMasterBOM: newPartsFromModelBOM, UnCommonPartsInModelBOM: newPartsFromMasterBOM });
    }

 This can be further approved on, once it's working.

The ComparisonService wouldn't be needed anymore.

[Rocko · Accepted solution]

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 });
    }

[AP_10343008]

Thanks for your efforts @Rocko . This code reduces the execution time. But the output doesnt included the uncommon columns in both infotables. Iam still working on bring back those columns in your code.

[Rocko]

That is weird because the calls to AddRow add the whole row, so all columns should be there. Did you use the correct datashapes instead of the "PUT_HERE_CORRECT_DATASHAPE" placeholder?

[AP_10343008]

Sorry i faced missing values in output for my work around. 

The actual error for the code you sent is :

Error occurred for this if condition

if (!MasterObject[code])
newPartsFromModelBOM.AddRow(r);
else
CommonObject[code]=true;

error:

JavaException: java.lang.Exception: Unable To Convert To Value Collection: Conversion Error on Field EFFECTIVEDATEFROM : Unable To Convert From java.lang.String to DATETIME

[Rocko]

Can't tell - these are not the variable names I used, also I haven't used EFFECTIVEDATEFROM anywhere. Maybe you made a mistake when renaming those.

[AP_10343008]

@Rocko 

Your suggestion of using let newPartsFromModelBOM=PartsFromModelBOM.cloneStructure(); worked instead of let newPartsFromModelBOM=DataShapes["PUT_HERE_CORRECT_DATASHAPE"] ....

I got correct output. And This code reduces execution time. it will be very useful for me.
Thanks a lot!

As I already marked my reply as solution, i couldn't mark your post as solution now.
Iam curious to find a way to mark your reply as effective solution.

[Catalina]

Hi @AP_10343008,

I wanted to see if you got the help you needed.

If so, please mark the appropriate reply as the Accepted Solution. It will help other members who may have the same question.
Please note that industry experts also review the replies and may eventually accept one of them as solution on your behalf.

Of course, if you have more to share on your issue, please pursue the conversation.

Thanks,