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

The community will undergo maintenance on October 16th at 10:00 PM PDT and will be unavailable for up to one hour.

How to combine infotables and their meta data

Willie
16-Pearl

How to combine infotables and their meta data

It seems that when I combine infotables with "union" their meta data do not get combined.  Is there a way to combine the meta data as well?  Meta data are fields such as source, timestamp, tags, location, etc.  The infotables are query results from a data table.  When I set the output of the service as "INFOTABLE" and "infotable type" to "is Data Table", I still do not get the meta data.

 

 

			let tableLength = someInfoT.rows.length;
			for (let x = 0; x < tableLength; x++) {
				let row = someInfoT.rows[x];
				//Your code here

				let query = {
					"filters": {
						"type": "And",
						"filters": [{
								"type": "EQ",
								"fieldName": "SomeID",
								"value": row.SomeID
							},
							{
								"type": "LT",
								"fieldName": "SomeDate",
								"value": oneYearAgo
							}
						]
					}
				};

				let queryResult = Things["SomeDataTable"].QueryDataTableEntries({
					maxItems: undefined /* NUMBER */ ,
					values: undefined /* INFOTABLE */ ,
					query: query /* QUERY */ ,
					source: undefined /* STRING */ ,
					tags: undefined /* TAGS */
				});

				// result: INFOTABLE
				result = Resources["InfoTableFunctions"].Union({
					t1: result /* INFOTABLE */ ,
					t2: queryResult /* INFOTABLE */
				});
			}

 

 

8 REPLIES 8
slangley
23-Emerald II
(To:Willie)

Hi @Willie.

 

Did you provide the full code snippet?  If so, it looks like you're missing one of the tables you're trying to join.

 

What version of ThingWorx are you running?

 

Regards

 

--Sharon

Hi @slangley 

 

No, I just included the pertinent lines as the service is a lot longer.

 

I wrote separate test services to see if infotable union includes meta data.  Based on my tests, it seems that it does if it is not in a for loop.  The output of the code below included meta data.

let query = {
   "filters":{
      "type": "EQ",
      "fieldName": "key",
      "value": 55
   }
}; 

let infoT1 =  Things["SomeDataTable"].QueryDataTableEntries({
	maxItems: undefined /* NUMBER */,
	values: undefined /* INFOTABLE */,
	query: query /* QUERY */,
	source: undefined /* STRING */,
	tags: undefined /* TAGS */
});

query = {
   "filters":{
      "type": "EQ",
      "fieldName": "key",
      "value": 60
   }
}; 

let infoT2 =  Things["SomeDataTable"].QueryDataTableEntries({
	maxItems: undefined /* NUMBER */,
	values: undefined /* INFOTABLE */,
	query: query /* QUERY */,
	source: undefined /* STRING */,
	tags: undefined /* TAGS */
});

// result: INFOTABLE
var result = Resources["InfoTableFunctions"].Union({
	t1: infoT1 /* INFOTABLE */,
	t2: infoT2 /* INFOTABLE */
});

query = {
   "filters":{
      "type": "EQ",
      "fieldName": "key",
      "value": 70
   }
}; 

let infoT3 =  Things["SomeDataTable"].QueryDataTableEntries({
	maxItems: undefined /* NUMBER */,
	values: undefined /* INFOTABLE */,
	query: query /* QUERY */,
	source: undefined /* STRING */,
	tags: undefined /* TAGS */
});

result = Resources["InfoTableFunctions"].Union({
	t1: result /* INFOTABLE */,
	t2: infoT3 /* INFOTABLE */
});

 

However, I noticed that when the infotable union occurs in a for loop, it does not include meta data.  The output of the code below did not include meta data.

var result = DataShapes["DataTableB.DS"].CreateValues();
let oneYearAgo = new Date() - 31536000000;
let infoTA = Things["DataTableA"].GetDataTableEntries({
	maxItems: 20 /* NUMBER */
});
let tableLength = infoTA.rows.length;
for (let x = 0; x < tableLength; x++) {
	let row = infoTA.rows[x];
	//Your code here

	let query = {
		"filters": {
			"type": "And",
			"filters": [{
					"type": "EQ",
					"fieldName": "SomeID",
					"value": row.SomeID
				},
				{
					"type": "LT",
					"fieldName": "SomeDate",
					"value": oneYearAgo
				}
			]
		}
	};

	let infoTB = Things["DataTableB"].QueryDataTableEntries({
		maxItems: undefined /* NUMBER */ ,
		values: undefined /* INFOTABLE */ ,
		query: query /* QUERY */ ,
		source: undefined /* STRING */ ,
		tags: undefined /* TAGS */
	});

	result = Resources["InfoTableFunctions"].Union({
		t1: result /* INFOTABLE */ ,
		t2: infoTB /* INFOTABLE */
	});
}

 

 

slangley
23-Emerald II
(To:slangley)

Hi @Willie.

 

In your 2nd example that doesn't include the metadata, it appears you have failed to combine tA and tB.  The t1:result doesn't exist:

 

result = Resources["InfoTableFunctions"].Union({ t1: result /* INFOTABLE */ , t2: infoTB /* INFOTABLE */

 

Regards.

 

--Sharon

 

Hi @slangley 

 

Not sure what you mean.  result is declared in the first line.  It is empty initially, but as it goes through the for loop, it gets rows added.

slangley
23-Emerald II
(To:slangley)

Hi @Willie.

 

It looks like you're performing a union with an empty info table.  You might want to review your code in the 2nd example where it's not working.

 

Regards.

 

--Sharon

slangley
23-Emerald II
(To:slangley)

Hi @Willie.

 

Have you been able to resolve your issue or do you still have questions?  Please let us know.

 

Regards.

 

--Sharon

So it seems that the union function has a limitation where if one of the infotables is empty, it will not add the meta data.

slangley
23-Emerald II
(To:slangley)

Hi @Willie.

 

Why would the infotable be empty?  What is the use case?  If needed, you could use some conditional logic to not perform the union if the infotable is empty.

 

Regards.

 

--Sharon

Announcements


Top Tags