Skip to main content
F
fgrondin1-Visitor
1-Visitor
July 18, 2017
Solved

Intersect InfoTables Update Value

  • July 18, 2017
  • 2 replies
  • 5985 views

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
    Best answer by CarlesColl

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

    });

    2 replies

    C
    CarlesColl1-Visitor
    1-Visitor
    July 18, 2017

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

    F
    fgrondin1-VisitorAuthor
    1-Visitor
    July 18, 2017

    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
    Y
    ytella17-Peridot
    17-Peridot
    July 18, 2017

    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?

    C
    CarlesColl1-Visitor
    1-Visitor
    July 18, 2017

    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.

    F
    fgrondin1-VisitorAuthor
    1-Visitor
    July 18, 2017

    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.

    Terms & ConditionsAccessibility statement