Community Tip - You can subscribe to a forum, label or individual post and receive email notifications when someone posts a new topic or reply. Learn more! X
1. It doesn't appear that I can create a datashape dynamically, it is a huge drawback to the pivot functionality (as you don't always know all the columns that are returned and it could be constantly changing)
2.Pivoted data may not have column names that conform with the Thingworx requirement (no spaces in column names) - also often times stored procedure column names have spaces (don't always have luxury of being able to modify a stored procedure to fit the software requirements (1 may be used for other applications, can't maintain 2 sps or can't add any additional sps) in them (I can go through the data and remove the spaces in the prepivoting scenario, but that is a pain)
3. Pivoted data requires a timestamp (not all pivot transformations require a timestamp pivot) - for instance you might have a beverages vs gender pivot table and there is no timestamp association with this data
Hi Justin, you'll be happy to know that CreateDataShape has been added as a Service in Entity Services in Thingworx 5.1 which has been released.
Thanks for the response on item 1. What about the other 2 items?
In 5.1 Spaces are now allowed. But also consider using build in abilities like "RenameField" to rename the fields, this would allow you to create more generic outputs as well.
Besides that you can use AddField or DeriveField to easily add a timestamp column.
These btw can all be found in the Wiki in Section 5, look for Resources/InfoTable functions.
I know during training you only have time to cover a few of these, so I encourage you to take a look at all the ones that are available. They should really help with the bringing in and processing of information within Thingworx.
Yes, I know how to derive a field and add a field but both of those would require a considerable amount of hardcoding (wish to avoid), so in the short term until spaces are resolved it is easier to just remove the spaces using replace(/\s+/g, '');
I think you misunderstood, I don't want or need a timestamp field (I have autogenerated a default one in the short term just to test the pivot (but it is pointless and unnecessary data). I need to pivot to return data like this
Resource Name ProjectName(s)
Original Data
Resource ProjectName Hours
Resource1 Project1 8
Resource1 Project2 12
Resource1 Project3 15
Resource1 Project1 21
Resource1 Project2 16
Resource1 Project3 13
Also additionally, for the charting it would seem that you should be able to dynamically link data to a chart (you don't want to always have to clearly define which columns to link prior to returning data as the dataset could change in the case of the pivot example above)
Next time you request data a new Project (Project 4 ) could have been added.
where the data you are pivoting is the Project Names so expected output would be:
ResourceName Project1 Project2 Project 3
Resource1 8 12 15
Resource2 21 16 13
How would you do this using the current pivot functionality (can't do this behind the scenes (using SQL 2000 which doesn't have the pivot functionality) as it limits me to one value column and one name column and is mandating a timestamp column when what I really want to use is my ResourceName column
I see.
Hi yes, I am using a label chart . It is not the chart that is demanding a timestamp, it is the pivot function that demands a timestamp and the pivot functionality that I currently don't see having the necessary functionality to accomplish the above example. If it can accomplish the transpose as shown above I would appreciate an example.
The charting comments were just additional questions/concerns I have about the flexibility of the current charting and the need to programmatically define the datafields as you won't always know all the fields that will potentially be returned.
Thanks!
Since Pivot specifically was created for ValueStreams right now it indeed only works with a datetime.
great thanks, will do.
If my understanding is correct regarding your question, I think that below link will be helpful for you.
Hi Justin,
I am not sure whether this will going to help you or not but I had somewhat same situation where I had a DataTable(Where real values are getting stored) and had to convert into something like this and then need to plot these on Time-Series Chart:
DateOfWeek Floors Number DateOfWeek F1 F2 F3
2/15/2017 F1 10 2/15/2017 10 20 30
2/16/2017 F2 20 2/16/2017 40 20 10
2/17/2017 F3 30 2/17/2017 20 30 40
2/15/2017 F1 40 convert/Pivot ....
2/16/2017 F2 20 =============>>
2/17/2017 F3 10
2/15/2017 F1 20
2/16/2017 F2 30
2/17/2017 F3 40
.....
So, for this first I had created one DataShape with four fields: test_Floor_Occupancy
1) Date // DateTime
2) F1 // Integer
3) F2 // Integer
4) F3 // Integer
Then I had created one service on that datatable: Floor_Occupancy_Details ( Output: Infotable, Datashape: test_Floor_Occupancy)
// Created InfoTable from DataShape "test_Floor_Occupancy"
var params = {
infoTableName : "InfoTable",
dataShapeName : "test_Floor_Occupancy"
};
// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(test_Floor_Occupancy)
var FinalResult = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
// GetAllDataTableEntries from your DataTable
var params = {
maxItems: undefined /* NUMBER */
};
// result: INFOTABLE
var result1 = Things["CarParkingWeekly"].GetDataTableEntries(params);
// InfoTable FOR Loop
var tableLength = result1.rows.length; // To get current number of rows or length of a DataTable
for (var x = 0; x < tableLength; x++) {
var rowX = result1.rows
var dateX = rowX.DateOfWeek; // yourinfotable.rows[0].columnName ==> will select the value of that column in 1st row of your Infotable
var F1 = 0; // Just to be sure no null values get pass on the final Infotable from which I'll going to plot the Time-Series Chart.
var F2 = 0;
var F3 = 0;
// test_Floor_Occupancy entry object
var newEntry = new Object();
newEntry.DateOfWeek = dateX; // DATETIME - isPrimaryKey = true
for(var y = 0; y < tableLength; y++) {
var rowY = result1.rows
var dateY = rowY.DateOfWeek;
if(dateY.getTime() == dateX.getTime() && rowY.BuildingName == "F1") {
F1 = rowY.Occupancy; // INTEGER
}
else if(dateY.getTime() == dateX.getTime() && rowY.BuildingName == "F2"){
F2 = rowY.Occupancy; // INTEGER
}
else if(dateY.getTime() == dateX.getTime() && rowY.BuildingName == "F3"){
F3 = rowY.Occupancy; // INTEGER
}
} // End of Inner For Loop (Y)
newEntry.F1 = F1;
newEntry.F2 = F2;
newEntry.F3 = F3;
FinalResult.AddRow(newEntry); // This will add the row(test_Floor_Occupancy) when all the remaining three fields gets its value for that particular // Date.
}
// This will give the final output after removing the duplicate rows (DISTINCT in Snippets)
var params = {
t: FinalResult /* INFOTABLE */,
columns: "DateOfWeek,F1,F2,F3" /* STRING */
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].Distinct(params); // final result
After getting the desired output, I had plot these points on my mashup using Time-Series Chart by calling this service (Floor_Occupancy_Details),.
Note: Coding can be optimized according to your need, It was an emergency for me, so I haven't dive into deep.
I hope this will work for you as well.
Thank you..