How to apply aggregate functions on grid data

[Rayon_11]

Hi all,

I have a doubt like suppose I  have some grid data for example suppose I have 3 column having column attribute like ID, Name and Type now I want to do some calculation on top of grid data like I want to show their number in percentage format.

For more detailed info I am attaching an image file

please help me 

Regards,

Rayon.

[Arun_C · Accepted solution]

Hi @Rayon_11 ,

Kindly find the below code it may helps you.

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

let AggregateData = Resources["InfoTableFunctions"].Aggregate(params);
var totalInfoLength = InputData.rows.length;

for (var a = 0; a < AggregateData.rows.length; a++) {
    var countType = AggregateData.rows[a].COUNT_Type;
    var typeName = AggregateData.rows[a].Type;
    var calculatedPercentage = ((countType / totalInfoLength) * 100).toFixed(0);
    
    for (var i = 0; i < InputData.rows.length; i++) {
        if (InputData.rows[i].Type === typeName) {
            InputData.rows[i].Percentage_Type = calculatedPercentage;
            InputData.rows[i].Category = typeName;
            break; 
        }
    }
}
result = InputData;

Input : 

Output : 

If any other queries please let me know.

Thanks & Regards,

Arun C

[Rayon_11]

Hi Arun,

 I did try with the code you provided just changed the Infotable name as I have set it to GridData but I am getiing error like

Error executing service TestAggregate. Message :: ReferenceError: "GridData" is not defined. - See Script Error Log for more detail

And my code is like:

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

let AggregateData = Resources["InfoTableFunctions"].Aggregate(params);
var totalInfoLength = GridData.rows.length;

for (var a = 0; a < AggregateData.rows.length; a++) {
var countType = AggregateData.rows[a].COUNT_Type;
var typeName = AggregateData.rows[a].Type;
var calculatedPercentage = ((countType / totalInfoLength) * 100).toFixed(0);

for (var i = 0; i < GridData.rows.length; i++) {
if (GridData.rows[i].Type === typeName) {
GridData.rows[i].Percentage_Type = calculatedPercentage;
GridData.rows[i].Category = typeName;
break;
}
}
}
result = GridData;

please let me know what changes I have to do 

Regards,

Rayon.

[Arun_C]

Hi @Rayon_11 ,

In my example InputData is a variable which I have declared in Service's Input as Infotable parameter. It act as source of Data. (Find the Input image section which I have already attached) 

In your case please get your source data in the variable of GridData or Create it in your service input based on your requirement.

Thanks &Regards,

Arun C

[Rayon_11]

This is the Input data which I have created in Thing  using Infotable property.

Input Data:

In the same Thing I created   service which you shared but still not working

Best Regards

Rayon.

[Arun_C]

Hi @Rayon_11 ,

Till you are facing "GridData" is not defined issue Or some other issue ? Also, Can you please confirm that GridData is an Thing Infotabale Property or Service Input Paramater? 

// Declared 'InputData' as variable to staore 'GridData' Property Values //
var InputData = me.GridData;

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

let AggregateData = Resources["InfoTableFunctions"].Aggregate(params);
var totalInfoLength = InputData.rows.length;

for (var a = 0; a < AggregateData.rows.length; a++) {
    var countType = AggregateData.rows[a].COUNT_Type;
    var typeName = AggregateData.rows[a].Type;
    var calculatedPercentage = ((countType / totalInfoLength) * 100).toFixed(0);
    
    for (var i = 0; i < InputData.rows.length; i++) {
        if (InputData.rows[i].Type === typeName) {
            InputData.rows[i].PercentageOfProduct = calculatedPercentage + '%';
            InputData.rows[i].Category = typeName;
            break; 
        }
    }
}
result = InputData;

Thanks & Regards,

Arun C

[Rayon_11]

Hi Arun,

Now I am getting the result, but the percentage column is not working. 

and  GridData is a Thing InfoTable Property 

I used just the above code now getting the output but Percentage coulumn is blank

Best Regards,

Rayon.

[Arun_C]

Hi @Rayon_11 ,

Have you checked all the ColumnNames are correct based on your datashape fields? Also please verify this 'PercentageOfProduct' is correct and its baseType 'STRING'.

InputData.rows[i].PercentageOfProduct = calculatedPercentage + '%';

If its not sloving your problem please share you Datashape in xml for further investigation.

Thanks & Regards,

Arun C

[Rayon_11]

Yes I have checked the column Name initially column PercentageOfProduct base type was number but now after changing it to string type still getting the same result and also other column has  base type like

ID-> Number

Type-> String

ProductName -> String

PercentageOfProduct -> String (Changed)

Category -String

 But still geting the same result.

Regards,

Rayon

[Arun_C]

Hi @Rayon_11 ,

I have tried multiple cases its working fine in my environment. Can you please share your Thing Entity and Datashape Entity for more investigation?

Also, Kindly refer the sample entities which I have prepared. 

1) Import Data Shape Entity

2) Import Thing Entity

3) Find service name of GetAggregatedData in thing - PTC.Community.Aggregate.TH

Thanks & Regards,

Arun C

[Rayon_11]

Thank you so much @Arun_C  for your reply it is working fine now I do have another doubt if you can, please help me in this

I have a doubt I am attaching file for detailed understanding 

I want to use pie chart and in that I want to configure it a way so that whenever I select any section of that pie chart It should render to the source it may be another pie chart or may be a grid data associated with that particular section similarly other section as well Now for that please tell us how can I proceed please also write the service as well for the data I have given it in the attached Image.

Best Regards,

Rayon

[Arun_C]

Hi @Rayon_11 ,

Based on my understanding I have created & attached your use case in demo entities. Kindly import the attachment in order (Datashapes, Thing & Mashup) and view the mashup of "PTC.Community.PieChart.MU" . It may clears your doubts.

Runtime:

If any queries please let me know.

Thanks & Regards,

Arun C

[Rayon_11]

Hi if possible can you please share me all the service again I am not able to write it

and also I am new to things so please tell how can I learn to write services

I have not much idea of javascript so please tell me if you have any resource or anything from where I can learn fast to write thingworx service and please do share the services which you created in above example and thank you so much for your speedy response such a nice person you are!

Best Regards,

Rayon.

[Rayon_11]

and please show the binding if possible