Community Tip - Your Friends List is a way to easily have access to the community members that you interact with the most! X
How to achieve excel like pivot functionality in Thingworx?
Tried using Pivot function, but it has limited functionality with dependency on timestamp property. So cannot achieve excel like pivot capability. Are there any other ways/options to achieve the same in ThingWorx?
var result = Resources["InfoTableFunctions"].Pivot(params);
Solved! Go to Solution.
Below is a sample working code as a reference for anyone looking to generate excel like pivot table successfully in Thingworx.
//1. Get the distinct list of column1
var params = {
t: result2 /* INFOTABLE */,
columns: "split" /* STRING */
};
var UniqueSplitList = Resources["InfoTableFunctions"].Distinct(params);
//2. Get the distinct list of column2
var params = {
t: result2 /* INFOTABLE */,
columns: "stage" /* STRING */
};
var UniqueStageList = Resources["InfoTableFunctions"].Distinct(params);
//3. Create an empty infotable using the target DataShape for the desired pivot table
var params = {
infoTableName : "InfoTable",
dataShapeName : "Stage_Splits_DataShape"
};
// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(Stage_Splits_DataShape)
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
//4. Loop through unique Column1 and Column2
for (var x = 0; x < UniqueSplitList.length; x++)
{
// new row entry object
var newEntry = new Object();
logger.warn("UniqueSplitList
newEntry.split = UniqueSplitList
for (var y = 0; y < UniqueStageList.length; y++)
{
//Generate the query to count occurrences in column2 for a given value in column1
var query2 = {
"filters": {
"type": "AND",
"filters": [
{
"fieldName": "split",
"type": "LIKE",
"value": UniqueSplitList
},
{
"fieldName": "stage",
"type": "LIKE",
"value": UniqueStageList
}
]
}
};
var params = {
t: result2 /* INFOTABLE */,
query: query2 /* QUERY */
};
// result3: INFOTABLE
var result3 = Resources["InfoTableFunctions"].Query(params);
//Calculate the counts for the current row
if(UniqueStageList
newEntry.no_two = result3.rows.length; // NUMBER
if(UniqueStageList
newEntry.no_three = result3.rows.length; // NUMBER
if(UniqueStageList
newEntry.no_four = result3.rows.length; // NUMBER
//Calculate the %s for the current row
if(y == UniqueStageList.length -1 )
{
newEntry.pct_two = Math.round(100 * newEntry.no_two/(newEntry.no_two + newEntry.no_three + newEntry.no_four)); // NUMBER
newEntry.pct_three = Math.round(100* newEntry.no_three/(newEntry.no_two + newEntry.no_three + newEntry.no_four)); // NUMBER
newEntry.pct_four = Math.round(100 * newEntry.no_four/(newEntry.no_two + newEntry.no_three + newEntry.no_four)); // NUMBER
//Add this entire row to the result infoTable
result.AddRow(newEntry);
}
}
}
Not as far as I know. But you may cook data to convert non timestamp fields to timestamp field:
Category = A = new Date(0)
Category = B = new Date(1)
...
Below is a sample working code as a reference for anyone looking to generate excel like pivot table successfully in Thingworx.
//1. Get the distinct list of column1
var params = {
t: result2 /* INFOTABLE */,
columns: "split" /* STRING */
};
var UniqueSplitList = Resources["InfoTableFunctions"].Distinct(params);
//2. Get the distinct list of column2
var params = {
t: result2 /* INFOTABLE */,
columns: "stage" /* STRING */
};
var UniqueStageList = Resources["InfoTableFunctions"].Distinct(params);
//3. Create an empty infotable using the target DataShape for the desired pivot table
var params = {
infoTableName : "InfoTable",
dataShapeName : "Stage_Splits_DataShape"
};
// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(Stage_Splits_DataShape)
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
//4. Loop through unique Column1 and Column2
for (var x = 0; x < UniqueSplitList.length; x++)
{
// new row entry object
var newEntry = new Object();
logger.warn("UniqueSplitList
newEntry.split = UniqueSplitList
for (var y = 0; y < UniqueStageList.length; y++)
{
//Generate the query to count occurrences in column2 for a given value in column1
var query2 = {
"filters": {
"type": "AND",
"filters": [
{
"fieldName": "split",
"type": "LIKE",
"value": UniqueSplitList
},
{
"fieldName": "stage",
"type": "LIKE",
"value": UniqueStageList
}
]
}
};
var params = {
t: result2 /* INFOTABLE */,
query: query2 /* QUERY */
};
// result3: INFOTABLE
var result3 = Resources["InfoTableFunctions"].Query(params);
//Calculate the counts for the current row
if(UniqueStageList
newEntry.no_two = result3.rows.length; // NUMBER
if(UniqueStageList
newEntry.no_three = result3.rows.length; // NUMBER
if(UniqueStageList
newEntry.no_four = result3.rows.length; // NUMBER
//Calculate the %s for the current row
if(y == UniqueStageList.length -1 )
{
newEntry.pct_two = Math.round(100 * newEntry.no_two/(newEntry.no_two + newEntry.no_three + newEntry.no_four)); // NUMBER
newEntry.pct_three = Math.round(100* newEntry.no_three/(newEntry.no_two + newEntry.no_three + newEntry.no_four)); // NUMBER
newEntry.pct_four = Math.round(100 * newEntry.no_four/(newEntry.no_two + newEntry.no_three + newEntry.no_four)); // NUMBER
//Add this entire row to the result infoTable
result.AddRow(newEntry);
}
}
}