10 Replies Latest reply on Feb 17, 2017 9:53 AM by smishra-3 RSS
    justiny Explorer

    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

      • Pivot Table and datashape creation
        paic Collaborator

        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.


        • Pivot Table and datashape creation
          justiny Explorer

          Thanks for the response on item 1.  What about the other 2 items?

            • Pivot Table and datashape creation
              paic Collaborator

              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.

            • Pivot Table and datashape creation
              justiny Explorer

              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

              • Pivot Table and datashape creation
                paic Collaborator

                I see.


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

                • Pivot Table and datashape creation
                  justiny Explorer

                  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.


                  • Pivot Table and datashape creation
                    paic Collaborator

                    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/

                    • Pivot Table and datashape creation
                      justiny Explorer

                      great thanks, will do.

                      • Re: Pivot Table and datashape creation
                        smishra-3 Explorer

                        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[x];               // 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[y];


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