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

Community Tip - Help us improve the PTC Community by taking this short Community Survey! X

From INFOTABLE to QueryPropertyHistory

DD_7886593
2-Guest

From INFOTABLE to QueryPropertyHistory

Hi i would like to insert my infotable data to QueryPropertyHistory service.

This is my data from my service.

Screenshot-2017-10-27 ThingWorx Composer.png

My thing Properties:

Screenshot-2017-10-27 ThingWorx Composer(1).png

My Value Stream Properties:

Screenshot-2017-10-27 ThingWorx Composer(3).png

My service code:

var params = {

    path: "k.csv" /* STRING */,

    columnMappings:"date_trunc;People_in_region",

    hasHeader: true /* BOOLEAN */,

    dateFormat: "dd.MM.yyyy HH:mm" /* STRING */,

    fileRepository: "SystemRepository",

    fieldDelimiter: ";" /* STRING */,

    stringDelimiter: "\"" /* STRING */,

    dataShape: "TFPeopledata" /* DATASHAPENAME */

};

var result = Resources["CSVParserFunctions"].ReadCSVFile(params);

The problem is when I execute the generic service "QueryPropertyHistory", I get nothing as result.

Screenshot-2017-10-26 ThingWorx Composer(3).png

1 ACCEPTED SOLUTION

Accepted Solutions

I have changed little bit in the code to get the result of service as an infotable:

var params = {

    path: "k.csv" /* STRING */,

    columnMappings:"date_trunc;People_in_region",

    hasHeader: true /* BOOLEAN */,

    //longitudeField: undefined /* NUMBER */,

    dateFormat: "dd.MM.yyyy HH:mm" /* STRING */,

    fileRepository: "SystemRepository",

    //latitudeField: undefined /* NUMBER */,

    fieldDelimiter: ";" /* STRING */,

    stringDelimiter: "\"" /* STRING */,

    dataShape: "TFPeopledata" /* DATASHAPENAME */

};

var res = Resources["CSVParserFunctions"].ReadCSVFile(params);

var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({infoTableName : "InfoTable",dataShapeName : "NamedVTQ"});

for each (var row in res.rows) {

    var newEntry = new Object();

    newEntry.name = "People_in_region";

    newEntry.time = row.date_trunc;

    newEntry.value = row.People_in_region;

    newEntry.quality = "GOOD";

    result.AddRow(newEntry);

}

var values = result;

var params = {

    values: values

};

me.UpdatePropertyValues(params);

Screenshot-2017-10-27 ThingWorx Composer(4).png

That's so great !


But when I run QueryPropertyHistory service, I get this result.

Screenshot-2017-10-27 ThingWorx Composer(5).png

The "date_trunc" values are not the same.

My objective is to work on startDate and endDate with the QueryPropertyHistory values.

View solution in original post

6 REPLIES 6
jamesm1
5-Regular Member
(To:DD_7886593)

If I understand what you are trying to do correctly, you want to take the infotable values and make them available as part of the QueryPropertyHistory service?

If so, do you have a Value Stream property set in the General Information section of the Thing? Then all you need to do is loop through your InfoTable and use the service UpdatePropertyValues.

Something like this should work:

infotable = //your infotable here

var values = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({infoTableName : "InfoTable",dataShapeName : "NamedVTQ"});

for each (var row in infotable.rows) {

    var newEntry = new Object();

    newEntry.name = "People_in_region";

    newEntry.timestamp = row.date_trunc;

    newEntry.value = row.People_in_region;

    newEntry.quality = "GOOD";

    values.AddRow(newEntry);

}

var params = {

values: values;

};

me.UpdatePropertyValues(params);

Hi James, Thank you,

The problem that I don't get all my data values, just a few of them.

Also should I put DataChange as "Value" or "on" ?

jamesm1
5-Regular Member
(To:DD_7886593)

In this case you want DataChange as "Always", since you want to log the property even if the value is the same as it was before, but it shouldn't matter if the example data set you provided was the full set -- they are all unique values -- it's very strange if you are not getting all of the values in this case. Could you try setting data change to "Always" and running the service again?

I have changed little bit in the code to get the result of service as an infotable:

var params = {

    path: "k.csv" /* STRING */,

    columnMappings:"date_trunc;People_in_region",

    hasHeader: true /* BOOLEAN */,

    //longitudeField: undefined /* NUMBER */,

    dateFormat: "dd.MM.yyyy HH:mm" /* STRING */,

    fileRepository: "SystemRepository",

    //latitudeField: undefined /* NUMBER */,

    fieldDelimiter: ";" /* STRING */,

    stringDelimiter: "\"" /* STRING */,

    dataShape: "TFPeopledata" /* DATASHAPENAME */

};

var res = Resources["CSVParserFunctions"].ReadCSVFile(params);

var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({infoTableName : "InfoTable",dataShapeName : "NamedVTQ"});

for each (var row in res.rows) {

    var newEntry = new Object();

    newEntry.name = "People_in_region";

    newEntry.time = row.date_trunc;

    newEntry.value = row.People_in_region;

    newEntry.quality = "GOOD";

    result.AddRow(newEntry);

}

var values = result;

var params = {

    values: values

};

me.UpdatePropertyValues(params);

Screenshot-2017-10-27 ThingWorx Composer(4).png

That's so great !


But when I run QueryPropertyHistory service, I get this result.

Screenshot-2017-10-27 ThingWorx Composer(5).png

The "date_trunc" values are not the same.

My objective is to work on startDate and endDate with the QueryPropertyHistory values.

jamesm1
5-Regular Member
(To:DD_7886593)

In this case, you really don't need the date_trunc property at all; you should be able to delete that property, purge the property history, and run the service to add the people_in_region as we did before, and you should get the correct values searchable by start and end date. The reason we don't need date_trunc is that it's just confusing things, since the query property history already *has* a timestamp value that we are updating when we pass in the named vtq to the updatepropertyvalues service.

James the problem is that the QueryPropretyHistory show random data !

This is the result of my service:

Screenshot-2017-10-27 ThingWorx Composer(6).png

and this is the result of QueryHistory:

Screenshot-2017-10-27 ThingWorx Composer(7).png

Not the same Data !

Top Tags