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

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

How to join 2 Infotables on their common columns

100% helpful (1/1)

Sometimes you need the values from different ThingTemplate members in ONE grid. Therefore it would be great, if you can join 2 "GetImplementedThingsWithData" results into a common one. Here a script that works generally as long as you don't mess with datatypes on same column names.

I'm very interested, if someone can find a much easier solution. The Union function was the only one I found suited for the task, but this needs preparation of the infotables upfront.

Input:

Table1 :Infotable

Table2: Infotable

Output: Infotable

Here the "Snippet":

// Define params for an Infotable to hold column names

var params = {

  infoTableName: "field" /* STRING */

};

// Define column 1

var newField = new Object();

newField.name = "field";

newField.baseType = 'STRING';

// Two 1 columns Infotables to store the field definition;

var field1 = Resources["InfoTableFunctions"].CreateInfoTable(params);

field1.AddField(newField);

var field2 = Resources["InfoTableFunctions"].CreateInfoTable(params);

field2.AddField(newField);

// Define the cell to add to Infotable

var myField = new Object();

myField.field = "";

myField.baseType = "STRING";

// Loop through Table1

var dataShapeFields = Table1.dataShape.fields;

for (var fieldName in dataShapeFields) {

  logger.debug('field1 name is ' + dataShapeFields[fieldName].name);

    myField.field = dataShapeFields[fieldName].name;

   field1.AddRow(myField);

}

// Loop through Table2

var dataShapeFields = Table2.dataShape.fields;

for (var fieldName in dataShapeFields) {

  logger.debug('field2 name is ' + dataShapeFields[fieldName].name);

   myField.field = dataShapeFields[fieldName].name;

   field2.AddRow(myField);

}

// Using inner join functionality to filter only the values that exist in both

var params = {

  columns2: "field" /* STRING */,

  columns1: "field" /* STRING */,

joinType: "INNER" /* STRING */,

  t1: field1 /* INFOTABLE */,

t2: field2 /* INFOTABLE */,

  joinColumns1: "field" /* STRING */,

  joinColumns2: "field" /* STRING */

};

var commonFields = Resources["InfoTableFunctions"].Intersect(params);

// Loop over the result to build a search string

var commonColumns = "";

var tableLength = commonFields.rows.length;

for (var x = 0; x < tableLength; x++) {

  var row = commonFields.rows;

  commonColumns = commonColumns + row.field + ",";

}

// Reduce Table1 to match only common columns

var params = {

t: Table1 /* INFOTABLE */,

columns: commonColumns /* STRING */

};

var result1 = Resources["InfoTableFunctions"].Distinct(params);


// Reduce Table2 to match only common columns

var params = {

  t: Table2 /* INFOTABLE */,

  columns: commonColumns /* STRING */

};

var result2 = Resources["InfoTableFunctions"].Distinct(params);

// At the END JOIN the tables together (does not work if colums are different)

var params = {

  t1: result1 /* INFOTABLE */,

  t2: result2 /* INFOTABLE */

};

var result = Resources["InfoTableFunctions"].Union(params);

Comments

Not sure if it would be faster, but you could just add the two together and then run a Distinct

Something a tat crazy but would work too, set up a DataTable with the proper Primary key and just do AddOrUpdate with both tables, this of course means you have it permanently stored.

Hi @PaiChung

Can I do it without a DataTable?

I have only one Infotable( created from a datashape) from which I have to create a lookup for  one of the fields...and then based on that particular field selection represent rest of the data in the mashup.

Also, explain how  I can do it with the help of list /dropdown widget...

 

 

If all the info even the info after selection is in the same infotable, I would just return the whole infotable to the mashup.

And then you can show the Items in a drop down and based upon selection use selected row information to display the associated information.

Thanks @PaiChung!

I tried the using my service and by calling  "QueryStreamentrieswithdata" and then triggering it with "SelectedRowschanged".

I got the required table.But now I want to customize the rows of the grid.

e.g if any one of the field of the table goes out of limit, that particular row should fill red(I don't want to show that deviated field in my grid)with all the texts showing up.

 

I have used state formatting(using"configure grid columns" of the grid widget).But, all the rows are showing red instead of the deviated ones

 

Also, I want to get the data every 5 min and display the data in Grid for every 5 min only.

Please, help me code the same in subscription.

 

To highlight different rows or cells based on value in a grid, you use State Based Formatting.

To apply it to a full row, in the Grid Properties select the Formatter and add your definition.

If you want it for a cell, then go into the Grid configuration (hover over drop down) find the appropriate column and go to formatting.

Hi! @PaiChung

I will look into it.I had been trying to do it through Grid Configuration.

Please also help me code in subscription so that I get my data every 5 min.

 

Thanks.

For every 5 minutes

server side use the Timer ThingTemplate and create a Timer Thing and then subscribe to that event.

client side use the AutoRefresh widget to re-execute the service call to get the data

Version history
Last update:
‎Apr 13, 2016 02:49 PM
Updated by:
Labels (1)