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

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

Pivot Table and datashape creation

justiny
1-Newbie

Pivot Table and datashape creation

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

10 REPLIES 10
paic
1-Newbie
(To:justiny)

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?

paic
1-Newbie
(To:justiny)

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




paic
1-Newbie
(To:justiny)

I see.

Only a TimeSeries chart would mandate a timestamp for the X-Axis though. wouldn't the Label chart suite your purpose?

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!

paic
1-Newbie
(To:justiny)

Since Pivot specifically was created for ValueStreams right now it indeed only works with a datetime.

I would encourage you to log a case for an enhancement with support: https://thingworx.ptc.com/appserver/cs/portal/

great thanks, will do.



YangJaeUng
5-Regular Member
(To:justiny)

If my understanding is correct regarding your question, I think that below link will be helpful for you.

https://www.google.co.kr/#newwindow=1&q=mssql+row+to+column

smishra-31
5-Regular Member
(To:justiny)

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;               // yourinfotable.rows[0]           ==>      selects 1st row of your InfoTable

   

    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..

Top Tags