Intersect and Derive Fields InfotableFunctions Error

[Jamal8548]

DATASHAPE 01

DATASHAPE 02

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

[Velkumar · Accepted solution]

Hi @Jamal8548 

Here you are passing wrong input

Based on your code, it should be Variable not Value

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

/VR

[Jamal8548]

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 4 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.