Intersect

[SP_10701000]

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

[Rocko]

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?

[SP_10701000]

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]

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

[SP_10701000]

This will return the matched rows not the unmatched rows.

[Rocko]

just make the statement then

if (!excludeList.includes(row.ACTIVITY_ID))