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

Combining 2 InfoTables

SOLVED
Highlighted
Level 9

Combining 2 InfoTables

Hello,

I want to combine 2 InfoTables and create a 3rd one that will combine both. I am trying to do it in an "elegant" way but so far I could get close doing iterations in the infotables.

Does anybody have a good approach for this?

So i have:

Table 1

Table 2:

Resulting Table (the "0" in the table could also be an "empty value", meaning that that date does not exist in that table:

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Combining 2 InfoTables

Hi Ewerton, I've built this service for this ( I have it on an Infotable Helpers thing ):

Service Name: FullJoin

Parameters:

  • infotable1
  • infotable2
  • joinFields (STRING) --> Comma separated list of fields to join on ( should have the same name in both infotables )
  • infotable2Fields (STRING) -> Comma separated list of fields to add from the infotable2.
  • infotable2BaseTypes (STRING) --> Comma separated list of fields baseTypes to add from the infotable2

Code:

var result = infotable1;

var fields2 = infotable2Fields.split(",");

var types2 = infotable2BaseTypes.split(",");

var join  = joinFields.split(",");

for(var i=0;i<fields2.length;i++) infotable1.AddField({ name: fields2, baseType: types2 });

var found,objFind = {};

for each(row in infotable1.rows) {

    objFind = {};

    for(var i=0;i<join.length;i++)  objFind[join] = row[join];

    found = infotable2.Find(objFind);

    if (found!=null) {

        for(var i=0;i<fields2.length;i++) row[fields2]=found[fields2];

        infotable2.Delete(found);

    }

}

for each(row in infotable2.rows) {

   result.AddRow(row);

}

7 REPLIES 7

Re: Combining 2 InfoTables

Replying my own question, so maybe you guys want to comment in my approach and help me were I got stuck: after a cup of coffee I decided to take a fresh look at this , and came up with a different approach.

  1. As both tables are generated by the same service with a different parameter, they both have the same field;
  2. I ran the service for generating each table and renamed the Value field to match its respective parameter;
  3. I then added a field to each table for the other parameter so both will have the same DataShape
  4. I then used UNION to combine both tables.

The issue I am running to is that now I have duplicate values for the common Column.

I tried to use the COMBINE method but did not figure it out yet.

Here's my code:

var report = me.EoCaDate();

var params = {

  propertyName: "NeedDate" /* STRING */,

  ReportResult: report /* INFOTABLE */

};

// result: INFOTABLE dataShape: "reportDS"

var needDate = me.aggregateReportsbyDate(params);

var params = {

  t: needDate /* INFOTABLE */,

  from: "Value" /* STRING */,

  to: "needDateQty" /* STRING */

};

// result: INFOTABLE

var needDateMod = Resources["InfoTableFunctions"].RenameField(params);

var newField = new Object();

newField.name = "resolutionDateQty";

newField.baseType = 'NUMBER';

needDateMod.AddField(newField);

var params = {

  propertyName: "ResolutionDate" /* STRING */,

  ReportResult: report /* INFOTABLE */

};

// result: INFOTABLE dataShape: "reportDS"

var resolutionDate = me.aggregateReportsbyDate(params);

var params = {

  t: resolutionDate /* INFOTABLE */,

  from: "Value" /* STRING */,

  to: "resolutionDateQty" /* STRING */

};

// result: INFOTABLE

var resolutionDateMod = Resources["InfoTableFunctions"].RenameField(params);

var newField = new Object();

newField.name = "needDateQty";

newField.baseType = 'NUMBER';

resolutionDateMod.AddField(newField);

var params = {

  t1: needDateMod /* INFOTABLE */,

  t2: resolutionDateMod /* INFOTABLE */

};

// result: INFOTABLE

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

Re: Combining 2 InfoTables

Hi Ewerton, I've built this service for this ( I have it on an Infotable Helpers thing ):

Service Name: FullJoin

Parameters:

  • infotable1
  • infotable2
  • joinFields (STRING) --> Comma separated list of fields to join on ( should have the same name in both infotables )
  • infotable2Fields (STRING) -> Comma separated list of fields to add from the infotable2.
  • infotable2BaseTypes (STRING) --> Comma separated list of fields baseTypes to add from the infotable2

Code:

var result = infotable1;

var fields2 = infotable2Fields.split(",");

var types2 = infotable2BaseTypes.split(",");

var join  = joinFields.split(",");

for(var i=0;i<fields2.length;i++) infotable1.AddField({ name: fields2, baseType: types2 });

var found,objFind = {};

for each(row in infotable1.rows) {

    objFind = {};

    for(var i=0;i<join.length;i++)  objFind[join] = row[join];

    found = infotable2.Find(objFind);

    if (found!=null) {

        for(var i=0;i<fields2.length;i++) row[fields2]=found[fields2];

        infotable2.Delete(found);

    }

}

for each(row in infotable2.rows) {

   result.AddRow(row);

}

Re: Combining 2 InfoTables

OMG, I so love this community !

Carles, thank you very much for the feedback. You answered so many questions in one shot: not only you gave me a nice way to do it but you showed examples of using Find.

I was trying to use it but was not being able to manage the null results.

BTW, how did you paste the formatted code into the response body? Will do it next time.

Cheers

Ewerton

Re: Combining 2 InfoTables

To format code you should use Advanced Editor for post, which option only comes up if you view the original post in one page ( click on the original post link ).

Re: Combining 2 InfoTables

I see it, but I mean, you pasted everything with the Javascript formatting. I don't think your formatted everything manually. I am assuming you pasted it from somewhere and it kept its formatting.

Re: Combining 2 InfoTables

Step by step 1:

Captura de pantalla 2016-12-28 12.40.15.png

Step 2 ( >> + Syntax Highlighting + javascript ):

Captura de pantalla 2016-12-28 12.41.27.png

Step 3 just paste your code here:

Captura de pantalla 2016-12-28 12.43.04.png

Re: Combining 2 InfoTables

Thanks.. I did not know the Advanced Editor had this feature. I thought it just allowed to do it manually.

Cheers

Ewerton