7 Replies Latest reply on Dec 28, 2016 6:46 AM by ewertonm RSS
    ewertonm Creator

    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:

      • Re: Combining 2 InfoTables
        ewertonm Creator

        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
            carlesc Ninja

            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[i], baseType: types2[i] });
            var found,objFind = {};
            for each(row in infotable1.rows) {
                objFind = {};
                for(var i=0;i<join.length;i++)  objFind[join[i]] = row[join[i]];
                found = infotable2.Find(objFind);
                if (found!=null) {
                    for(var i=0;i<fields2.length;i++) row[fields2[i]]=found[fields2[i]];
                    infotable2.Delete(found);
                }
            }
            
            
            for each(row in infotable2.rows) {
               result.AddRow(row);
            }
            
            
            
              • Re: Combining 2 InfoTables
                ewertonm Creator

                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
              carlesc Ninja

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