7 Replies Latest reply on Jul 19, 2017 2:34 AM by carlesc RSS
    fgrondin Explorer

    Intersect InfoTables Update Value

    Hi,

    Let's say I have these 2 tables.

     

    I have 2 questions.

    1. How can I get the row where Value is empty in Table 1 ?
    2. How can I get result like the Table 3 Final Result ?
    3. What would be the faster / most efficient way to get the result that I want ?

     

    My Case:

    - I'm getting Table 1 from one database .

    - I'm getting Table 2 from another database which has Ids list as Input. So "2,5".

    - I join both tables to get Table 3 Final Result

     

    I know I could do some loop to get the results that I want, but when you have quiet a lot of rows, the loop becomes slow.

    I've tried the Intersect method but I couldn't get the result that I wanted (Maybe I'm doing it wrong).

     

    Table 1:

    IDValue
    110
    2
    330
    440
    5

     

    Table 2:

    IDValue
    220
    550

     

    Table 3 Final Result:

    IDValue
    110
    220
    330
    440
    550
      • Re: Intersect InfoTables Update Value
        carlesc Heavyweight Champ

        You may filter first empty rows, and then Union.

         

        // Filter Empty Rows on Table 1

        var result = Resources["InfoTableFunctions"].MissingValueFiler({

          t: Table1,

          inclusive: false,

          filedName: "value"

        });

         

        // Union both infotables

        result = Resources["InfoTableFunctions"].Union({ t1: result, t2: Table2 });

          • Re: Intersect InfoTables Update Value
            fgrondin Explorer

            So If I understand, you are removing the rows that have missing value.

            Then, with the result of Table 2 you add the rows at the end.

            That would work.

             

            But what if I have something like this ?

            I don't think the method you have describe will work, because the two other columns will be empty.

             

            Table 1

            IDValueData_AData_B
            110a1b1
            2a2b2
            330a3b3
            440a4b4
            5a5b5
            660a6b6

             

            Table 2

             

            IDValue
            220
            550
            Table 3 Final Result
            IDValueData_AData_B
            110a1b1
            220a2b2
            330a3b3
            440a4b4
            550a5b5
            660a6b6
          • Re: Intersect InfoTables Update Value
            ytella Creator

            Hi Frederik, Could you please provide more details on this. Does Table 2 contain only the IDs for which the value is empty in Table 1?

              • Re: Intersect InfoTables Update Value
                carlesc Heavyweight Champ

                Of course, I gave you what you asked for

                 

                As Yamini said, better you explain in more detail what do you want to do and which it's the nature of the data.

                  • Re: Intersect InfoTables Update Value
                    fgrondin Explorer

                    Yes,

                    So basically, What I intend to do with that is :

                    I have a main database which will return a result (Table 1).

                    For some row inside the table A, there will be cells that are empty.

                     

                    For each cell that are empty, I need to go execute another service on second database to get the missing data which will return as output an infotable.

                    That service has a comma seperated list of all the ID that has a missing value so "2,5". The Stored procedure will then create a table with the id and the value (Table 2).

                     

                    Then I to update table 1 with the result of table 2 in order to get a table that contains all the data without any missing value (Table 3).

                    This is why I tried to do an Intersect with LEFT Join to update, but it updates everything. What I want is update Cell if cell is empty.

                      • Re: Intersect InfoTables Update Value
                        fgrondin Explorer

                        Right now what I have is

                        I have two Sps,

                        First SP coming from database 1 : Returns everything but the Value,

                        Second SP which can come from database 1 and different sources : Has ID as input and return the value as output .

                         

                        So I would loop through the result of the first SP and for each row I would call the Second SP, put the Id as input and it will return the Value. Then I can update the Object.Value of that row.

                         

                        There are problem with it : it takes more or less 30 seconds for only 700 rows.

                        And Since there are 700 rows, I would execute the second SP 700 Times which can cause problem to the server.

                        But it's working, but it is a bad practice to execute a SP a lot of time when you can minimize it and I would like to reduce the time it takes.



                        Sometimes I would need to execute the second stored procedure on another database. So maybe Row 1 = database 1 , Row 2 = database 2...

                         

                        What I would do is

                        This is why I would like to have the value already populated for the rows that comes from database 1.

                        Then for the rows that can't have the value, since the value come from another database, I would do a list of all Id that are on the same database and execute it once to have a new table and join / update to the first table that I have. Then, the table would be completed.

                          • Re: Intersect InfoTables Update Value
                            carlesc Heavyweight Champ

                            Hi Frederik,

                             

                            First of all, 30 seconds for 700 rows can't be possible, you should have another bottle neck on your code.

                             

                            Anyway, if you want to use TW Services to do it ( which will be executed in Java and should be faster ) what I would do:

                             

                            var it1; // It's your full infotable with missing values

                            var it2;// It's your fill the holes infotable

                             

                            it2 = Resources["InfoTableFunctions"].RenameField({ t: it2, from: "value", to: "fillGapsValue" }); //-- If you are generating this infotable you may generate it with a different column name and you can avoid this step.

                             

                            var result = Resources["InfoTableFunctions"].Intersect({

                               columns1: "*",

                               columns2: "fillGapsValue",

                               joinType: "LEFT",

                              joinColumns1: "ID",

                              joinColumns2: "ID",

                               t1: it1,

                               t2: it2,

                            });

                             

                            result = Resources["InfoTableFunctions"].DeriveFields({

                               t: result,

                              types: "NUMBER",

                              columns: "value",

                            expressions: "(((value==undefined)||(value==null))?fillGapsValue:value)"

                            });