cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X

Intersect InfoTables Update Value

fgrondin
5-Regular Member

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
1 ACCEPTED SOLUTION

Accepted Solutions

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

});

View solution in original post

7 REPLIES 7

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

fgrondin
5-Regular Member
(To:CarlesColl)

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
ytella
17-Peridot
(To:fgrondin)

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?

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.

fgrondin
5-Regular Member
(To:CarlesColl)

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.

fgrondin
5-Regular Member
(To:fgrondin)

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.

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

});

Top Tags