Pivot Table and datashape creation

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?

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.

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