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

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

Join (at least) 2 Property History Infotables by Timestamp

TomC42
3-Visitor

Join (at least) 2 Property History Infotables by Timestamp

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.

1 ACCEPTED SOLUTION

Accepted Solutions
TomC42
3-Visitor
(To:TomC42)

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.

View solution in original post

4 REPLIES 4
slangley
23-Emerald II
(To:TomC42)

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
3-Visitor
(To:slangley)

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
3-Visitor
(To:TomC42)

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
23-Emerald II
(To:TomC42)

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

Top Tags