Skip to main content
E
emoreira14-Alexandrite
14-Alexandrite
December 27, 2016
Solved

Combining 2 InfoTables

  • December 27, 2016
  • 2 replies
  • 11171 views

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:

Best answer by CarlesColl

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);
}

2 replies

E
emoreira14-AlexandriteAuthor
14-Alexandrite
December 27, 2016

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);
    C
    CarlesColl1-VisitorAnswer
    1-Visitor
    December 28, 2016

    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);
    }
      E
      emoreira14-AlexandriteAuthor
      14-Alexandrite
      December 28, 2016

      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

        C
        CarlesColl1-Visitor
        1-Visitor
        December 28, 2016

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

        E
        emoreira14-AlexandriteAuthor
        14-Alexandrite
        December 28, 2016

        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.

        C
        CarlesColl1-Visitor
        1-Visitor
        December 28, 2016

        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

          Terms & ConditionsAccessibility statement