Combining 2 InfoTables

[emoreira]

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:

[emoreira]

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

[CarlesColl · Accepted solution]

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

[emoreira]

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

[CarlesColl]

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

[emoreira]

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.

[CarlesColl]

Step by step 1:

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

Step 3 just paste your code here:

[emoreira]

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

Cheers

Ewerton