Skip to main content
S
SP_107010004-Participant
4-Participant
January 10, 2024
Question

Intersect

  • January 10, 2024
  • 1 reply
  • 1751 views

I have requirement to find unmatched rows from two tables.

Basically need to compare one table data to another table and save the unmatched records from first table into new table.

I used intersect to solve this.

but getting Unable To Convert From org.mozilla.javascript.UniqueTag to INFOTABLE error.

 

 
try {
let Status = 'Accepted';
let Project;
let WorkPackage;
let WorkOrder;
let Jobcard;
let InsertProjectData;
let currentuser = Resources["CurrentSessionInfo"].GetCurrentUser();
var currentdate = Date.now();
 
 
let EIDWData = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
infoTableName: "InfoTable",
dataShapeName: "DWM_ DB_EIDWACTIVITYVERSION_DS"
});
let DWMProjectData = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
infoTableName: "InfoTable",
dataShapeName: "DWM_DB_PROJECT_DS"
});
 
let DWMWorkPackageData = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
infoTableName: "InfoTable",
dataShapeName: "DWM_DB_WorkPackage_DS"
});
let DWMWorkOrderData = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
infoTableName: "InfoTable",
dataShapeName: "DWM_DB_WorkOrder_DS"
});
let DWMJobCardData = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
infoTableName: "InfoTable",
dataShapeName: "DWM_DB_JOBCARD_DS"
});
//Get Accepted JobCards from EIDW database
EIDWData = me.GetActivityVersionData({
Status: Status /* STRING */
});
 
//Get Project details from Application database
DWMProjectData = Things["DWM_Database_AppDBThing"].GetProjectData({
Status: Status /* STRING */
});
 

 

 
 
 
 
let params = {
columns2: "ProjectId,ProjectDescrption,Status" /* STRING */,
columns1: "ACTIVITY_ID,DESCR,STATUS,SUPERVISOR,PLANNER" /* STRING */,
joinType: "inner" /* STRING {"defaultValue":"INNER"} */,
t1: EIDWData /* INFOTABLE */,
t2: DWMProjectData /* INFOTABLE */,
joinColumns1: "ACITIVITY_ID" /* STRING */,
joinColumns2: "PROJECTID" /* STRING */
};
logger.error("InsertionOfActivityVersionData : intersect" + columns2);
// result: INFOTABLE
result = Resources["InfoTableFunctions"].Intersect(params);
 
 
 
 
 
 
 
 
 
 

1 reply

Rocko
Rocko19-Tanzanite
19-Tanzanite
January 10, 2024

Some observations:

-If you assign the Infotables with the result of a service, there is no need to Create them previously.

-There is a more elegant way to create infotables than CreateInfoTableFromDataShape, e.g.:

let DWMWorkPackageData = DataShapes["DWM_DB_WorkPackage_DS"].CreateValues();

-as Jointype use "INNER" not "inner"

-Is there a typo in joinColumns1? ACITIVITY_ID instead of ACTIVITY_ID?

S
SP_107010004-ParticipantAuthor
4-Participant
January 10, 2024

Hi thanks for the reply.

Actually typo error is solved but my doubt is if I use INNER join it is returing common rows. But my requirement is to get unmatched rows from table 1 and display it as result .

Is there is any other Join I can use

Rocko
Rocko19-Tanzanite
19-Tanzanite
January 10, 2024

I was more focusing on the capitalisation of "inner" jointype than what the code does. There is no readymade "minus" operation. But if you want to something like that, you could go roughly with something like this:

 

//Get Accepted JobCards from EIDW database
let eIDWData = me.GetActivityVersionData({Status: Status});

//Get Project details from Application database
let dWMProjectData = Things["DWM_Database_AppDBThing"].GetProjectData({Status: Status});

let excludeList=[];
dWMProjectData.rows.toArray().forEach(row=>{
excludeList.push(row.PROJECTID);
});
result=DataShapes["DWM_ DB_EIDWACTIVITYVERSION_DS"].CreateValues();
eIDWData.rows.toArray().forEach(row=>{
if (excludeList.includes(row.ACTIVITY_ID))
result.AddRow(row);
});

 

 

Terms & ConditionsAccessibility statement