Skip to main content
A
Aditya170216-Pearl
16-Pearl
March 19, 2024
Solved

Calculating how many times a property value repeats in QueryPropertyHistory Service

  • March 19, 2024
  • 1 reply
  • 3372 views

Hi,

I have logged property into a value stream and using QueryPropertyHistory service I am able to have a view at the recorded data of the logged properties. What next I wan to achieve is how can we calculate the number of times a value of a property repeats in the data recorded, upto a particular date.

 

eg, I have property called stepValueStatus which has a string value pass/fail. So I want to calculate how many times pass occurs and how many times fail occurs for this stepValueStatus property.

 

Thanks in Advance.

 

Regards,

Aditya Gupta

Best answer by Velkumar

Hi @Aditya1702 

 

Set your service Output as InfoTable and set DataShape to it.

 

In Mashup, Call this service and map output to Grid Widget.

Velkumar_0-1710842053041.png

 

 

/VR

1 reply

V
19-Tanzanite
Velkumar19-Tanzanite
19-Tanzanite
March 19, 2024

Hi @Aditya1702 

 

You can use the Aggregate Function to get a count of values

 

let params = {
 t: me.dummyInfoTable/* INFOTABLE */,
 columns: "name" /* STRING */,
 aggregates: "COUNT" /* STRING */,
 groupByColumns: "name" /* STRING */
};

// result: INFOTABLE
let result = Resources["InfoTableFunctions"].Aggregate(params);

Input:

Velkumar_1-1710833891309.png

 

Output :

Velkumar_0-1710833856610.png

 

/VR

 

 

    A
    Aditya170216-PearlAuthor
    16-Pearl
    March 19, 2024

    Hi @Velkumar ,

    What to add in place of dummyInfotable since I don't have any infotable I get infotable as an output of QueryPropertyHistory Service.

    Can you please elaborate a bit more since, I am new to ThingWorx.

     

    Thanks in Advance.

     

    Regards,

    Aditya Gupta

     

    V
    19-Tanzanite
    Velkumar19-Tanzanite
    19-Tanzanite
    March 19, 2024

    Hi @Aditya1702 

     

    Replace me.dummyInfoTable with an output of QueryPropertyHistory service

     

    var data = me.QueryPropertyHistory({
 maxItems: undefined /* NUMBER {"defaultValue":500} */,
 startDate: undefined /* DATETIME */,
 endDate: undefined /* DATETIME */,
 oldestFirst: undefined /* BOOLEAN */,
 fillOption: undefined /* STRING {"defaultValue":"Previous"} */,
 query: undefined /* QUERY */
});


let params = {
 t: data /* INFOTABLE */,
 columns: "FIELDNAME_TO_COUNT" /* STRING */,
 aggregates: "COUNT" /* STRING */,
 groupByColumns: "FIELDNAME_TO_COUNT" /* STRING */
};

// result: INFOTABLE
let result = Resources["InfoTableFunctions"].Aggregate(params);

     

    /VR

      Terms & ConditionsCookie settingsAccessibility statement