Join (at least) 2 Property History Infotables by Timestamp

[TomC42]

Hi,

So I have a mashup which selects a number of properties for a thing. It then loops through each of these properties and Queries the property history. The goal is to take each history infotable and add it to an overall history table with all of the properties and timestamps. The following tables may give more of an idea:

table1 - overall table (starts without any values)

timestamp

Property1

table2 - property1 history table

timestamp	Property1
1	x
2	y

So after the first iteration table1 will look the same as table2. For the second iteration/property the overall table should become:

timestamp	Property1	Property2
1	x
2	y

Table3 - property2 history table

timestamp	Property2
1	z
3	x

After joining the overall table to the second property table, it should become:

timestamp	Property1	Property2
1	x	z
2	y	-
3	-	x

The method for this should work for any number of properties. I tried using a full join then a distinct but the tables just returned empty after the join.

Here is essentially what I have currently (doesn't work). Note that there may be a completely different method to reaching that final overall table.

 var params = {
       infoTableName : "InfoTable",
       dataShapeName : "Reports_OverallHistory_datashape"
};

//CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(Reports_OverallHistory_datashape)
var allHistory = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

var length = joinedPropertiesTable.rows.length;
for (var i = 0; i < length; i++){
       var property = joinedPropertiesTable.rows[i];
       var params = {
               name: property.name /* STRING */
        };

        // result: INFOTABLE dataShape: PropertyDefinition
        var singlePropTable = Things[asset].GetPropertyDefinition(params);
            
        var params = {
             oldestFirst: oldestFirst /* BOOLEAN */,
             maxItems: maxHis /* NUMBER */,
             endDate: endDate /* DATETIME */,
             propertyNames: singlePropTable /* INFOTABLE */,
             query: undefined /* QUERY */,
             startDate: startDate /* DATETIME */
         };

        //Get Property History for selected assets
        // result: INFOTABLE
        var singlePropertyHistory = Things[asset].QueryNamedPropertyHistory(params);
            
         var newField = new Object();
         newField.name = property.name;
         newField.baseType = singlePropTable.baseType;
         allHistory.AddField(newField);
                
          var params = {
               columns2: "*" /* STRING */,
               columns1: "*" /* STRING */,
               joinType: "FULL" /* STRING */,
               t1: allHistory /* INFOTABLE */,
               t2: singlePropertyHistory /* INFOTABLE */,
               joinColumns1: "timestamp" /* STRING */,
               joinColumns2: "timestamp" /* STRING */
            };

            // result: INFOTABLE
            allHistory = Resources["InfoTableFunctions"].Intersect(params); 
            }
var result = allHistory;

The reason I don't simply query property history with multiple properties all at once, is because I want the exact timestamps for each property and not the value at a time which wasn't logged for one property but was the other.

[slangley]

Hi @TomC42.

If we are understanding your use case, you may be able to use the Advanced Grid widget available on the Marketplace to accomplish this.  Note:  you will need to validate the version and if necessary install an older version of the widget.  The use of this widget will be more efficient as it will make one call to the database as opposed to the many you described in your example.  If you have any questions around the use of this widget, please let us know.

Please take a look at this and determine if this meets your needs.  If not, please provide further details and we will try to assist.

Regards.

--Sharon

[TomC42]

Hi,

I appreciate the response, however that isn't really relevant for my issue at all. I have actually managed to solve this myself, and I will follow up with a response to my original post on how I managed it.

Thanks

[TomC42 · Accepted solution]

After putting some more thought in after making the post I managed to get what I was looking for. I achieved it by isolating all of the timestamps for each of the properties into it's own infotable, using the Distinct function in order to narrow it down so there was no duplicates, and finally using a LEFT JOIN between the timestamps table and each of the properties (with all of the columns from the property table included in the join). For each iteration of the LEFT JOIN you must ensure that the timestamp table is set to the result.

[slangley]

Hi @TomC42.

Glad you were able to resolve your issue and thank you for posting the solution.

Please mark your response as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon