cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

Intersect and Derive Fields InfotableFunctions Error

Jamal8548
12-Amethyst

Intersect and Derive Fields InfotableFunctions Error

DATASHAPE 01

Jamal8548_0-1720616259251.png

DATASHAPE 02

Jamal8548_1-1720616287846.png

My service:= I am having two infotables. In first infotable i have no Criteria key value in the Infotable and in second infotable i have this Criteria and now i am doing this with infotable functions. but i have an error
: Script evaluation error : ReferenceError: "Criteria" is not defined. (DSLCompiledExpressionProcessor#1)"

 

"

 

 

let TableDynamicData = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
		infoTableName: "InfoTable",
		dataShapeName: "Jamal.DS"
	});

let TableDynamicDataTwo = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
		infoTableName: "InfoTable",
		dataShapeName: "JamalOne.DS"
	});


let jsonData = {

            "UserResponse": {
                "I_O": "",
                "Criteria": "",
                "Image": [],
                "Remark": "1688977680000"
            },
             "QID": "1_1",
             "I_O": "",
             "Image": [],
             "Remark": "1688977680000",
             "I_O_Image":"jamal"
        
        
};

TableDynamicData.AddRow(jsonData);
let jsonData2 = {


             "QID": "1_1",
             "Criteria": "Test Successful",
         
};
TableDynamicDataTwo.AddRow(jsonData2);


//let result = TableDynamicData;
	let fieldNamefromStaticTable = 'QID,I_O,Image,Remark,I_O_Image,UserResponse';
    let fieldNameForDynamicTable = 'Criteria';

let tempData = Resources["InfoTableFunctions"].Intersect({

   columns1: "fieldNamefromStaticTable",
   columns2: "fieldNameForDynamicTable",
   joinType: "LEFT",
   joinColumns1: "QID",
   joinColumns2: "QID",
   t1: TableDynamicData,
   t2: TableDynamicDataTwo,

});


		params = {
			types: 'STRING' /* STRING */ ,
			t: tempData /* INFOTABLE */ ,
			columns: 'Criteria',
			expressions: "(Criteria==undefined)?'':Criteria"
		};
		result = Resources["InfoTableFunctions"].DeriveFields(params);

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Jamal8548 

 

Here you are passing wrong input

Velkumar_0-1720676587304.png

 

Based on your code, it should be Variable not Value

Velkumar_1-1720676627726.png

 

Modified Code :

let TableDynamicData = me.TESTQUERY2();

let TableDynamicDataTwo = me.TESTQUERY();

//let result = TableDynamicData;
let fieldNamefromStaticTable = 'customerName';
let fieldNameForDynamicTable = 'orderId,orderDescription,customerId';

let tempData = Resources["InfoTableFunctions"].Intersect({

	columns1: fieldNamefromStaticTable,
	columns2: fieldNameForDynamicTable,
	joinType: "LEFT",
	joinColumns1: "customerId",
	joinColumns2: "customerId",
	t1: TableDynamicData,
	t2: TableDynamicDataTwo,

});

let result = tempData;

params = {
	types: 'STRING' /* STRING */ ,
	t: tempData /* INFOTABLE */ ,
	columns: 'orderId',
	expressions: "(orderId== null) ? 0 : orderId"
};
result = Resources["InfoTableFunctions"].DeriveFields(params);

 

Velkumar_0-1720677121331.png

 

 

/VR

 

 

View solution in original post

2 REPLIES 2

Hi @Jamal8548 

 

Here you are passing wrong input

Velkumar_0-1720676587304.png

 

Based on your code, it should be Variable not Value

Velkumar_1-1720676627726.png

 

Modified Code :

let TableDynamicData = me.TESTQUERY2();

let TableDynamicDataTwo = me.TESTQUERY();

//let result = TableDynamicData;
let fieldNamefromStaticTable = 'customerName';
let fieldNameForDynamicTable = 'orderId,orderDescription,customerId';

let tempData = Resources["InfoTableFunctions"].Intersect({

	columns1: fieldNamefromStaticTable,
	columns2: fieldNameForDynamicTable,
	joinType: "LEFT",
	joinColumns1: "customerId",
	joinColumns2: "customerId",
	t1: TableDynamicData,
	t2: TableDynamicDataTwo,

});

let result = tempData;

params = {
	types: 'STRING' /* STRING */ ,
	t: tempData /* INFOTABLE */ ,
	columns: 'orderId',
	expressions: "(orderId== null) ? 0 : orderId"
};
result = Resources["InfoTableFunctions"].DeriveFields(params);

 

Velkumar_0-1720677121331.png

 

 

/VR

 

 

Jamal8548
12-Amethyst
(To:Velkumar)

Thank you for your detailed answer! I have fixed the problem at my side. Now i would like to write on it for clarification so that may be it will help new comers also. @Velkumar Please correct me if something is not right...?

 

INTERSECT FUNCTION

We use Intersect function to combine the rows from two infotables so if i have two infotables 

 

infotable 1:- ID, customerName, Address

infotable 2:- ID, orderId,orderDescription,customerId

 

let fieldNamefromStaticTable = 'customerName';
let fieldNameForDynamicTable = 'orderId,orderDescription,customerId';

 

 

let tempData = Resources["InfoTableFunctions"].Intersect({

	columns1: fieldNamefromStaticTable,
	columns2: fieldNameForDynamicTable,
	joinType: "LEFT",
	joinColumns1: "customerId",
	joinColumns2: "customerId",
	t1: TableDynamicData,
	t2: TableDynamicDataTwo,

});

let result = tempData;

 

 

Case 1: now even though in Infotable 1 we have address but Intersect will not return us this because from that infotable we have mentioned that we need 'customerName' only and from second one we mentioned that we need  'orderId,orderDescription,customerId' So it will only return these properties.

 

Case 2: If any one of the entry from both infotables would NOT have orderId means null like orderId:"" then we will get the result of only from first Infotable like above orderId and customerName we got for the last entry but we have to write a condition in DeriveField. Because we are merging/reading the values on behalf of IDs as we have mentioned them in intersect function.

 

 

 

Case 3: We have write the field name from Infotable 1 if we want it also in the result otherwise we could not get it. We only get the fields which we will mention only as we did in fieldNamefromStaticTable and fieldNameForDynamicTable 

 

Case 4: We can write as many times the DeriveFields function to add more conditions 

 

params = {
	types: 'STRING' /* STRING */ ,
	t: tempData /* INFOTABLE */ ,
	columns: 'orderId',
	expressions: "(orderDescription== order1) ? 23 : 55"
};

 

 

as above i write an addition condition again so that i can manipulate the orderDescription only for order1 so it finds this then it  will assign it 23 otherwise 55 or orderDescription what we are getting there. 

 

I have tried myself to write few conditions to cover all aspects of these two functions to clear my confusions and for future developers. Please correct me if something is not right or clear. If you have any additional usecase in mind please let me know. 

 

 

Top Tags