Compare the same columns between 2 rows to find columns where the value is different

[Alpha549]

Hello everyone,

here is the case.

I would like to track updates between two rows.

Here is an example :

Row 1 :

ID : 1

Firstname : Rice

Age : 18

Power : 27

Row 2 :

ID : 1

Firstname : Rice

Age : 18

Power : 28

I would like to detect that the power has changed, and that the old value is 27, and the new one is 28.

I'm not talking about SCD here with SCD component. I would like to know if there is another way. Here via a join in a tMap, I'll have old values and new values, but here the row has actually around 70 columns so comparing columns is difficult.

Do you have any clue ?

Thank you 🙂

[Anonymous · Accepted solution]

There is a nice way of doing this with a tMap component. Knowing this actually opens up a few more possibilities with the tMap. Let's assume I have the following values in my two records.....

New Values

Row; ID; Name; Age; Power

1 ; 1; Bob; 27; 12

1 ; 2; James; 32; 14

1 ; 3; Jane; 42; 23

Old Values

Row; ID; Name; Age; Power

2 ; 1; Bob; 27; 1

3 ; 1; Bob; 27; 23

2 ; 2; James; 32; 140

3 ; 2; James; 32; 12

2 ; 3; Jane; 42; 29

3 ; 3; Jane; 42; 32

The first thing I do is join the records into one set. I have done this in my example using a tUnite component.

Then, I have to order the components by ID and Row to ensure that the records come in ordered correctly. I use a tSortRow for this.

The next step is the tMap. This is where the logic takes place. The key to this is knowing that the tMap variables are processed sequentially from top to bottom AND they are kept the same unless they are changed. This is important. Here is my tMap....

 

Notice I have added a new column on the output called "PowerDifference". I'll describe the tMap variables one at a time, in order, below.....

OldId

This takes the value of the next variable called "ThisId". For the first row, this value will not have been set yet. So the first row will have a blank "OldId". Hence "Nullable" is ticked.

Code:

Var.ThisId

ThisId

This takes the value of the ID for the incoming record. At this point in processing, the "OldId" will either hold null or the previous row's ID. "ThisId" will hold the current row's ID.

Code:

row4.ID

PowerDifference

This calculates the difference between the incoming "Power" column value and the "LastRowPower" variable which will be set next. Remember, the values in tMap variables are held between rows. So the "LastRowPower" variable will either hold null or the last row value. There is an inline IF condition on this as well. If the "OldId" is not null and "ThisId" equals "OldId" then perform the calculation, otherwise return 0.

Code:

Var.OldId!=null && Var.ThisId==Var.OldId ? row4.Power - Var.LastRowPower : 0 

LastRowPower

This simply sets the value of the "LastRowPower" variable to be the current row's "Power" value AFTER all calculations have taken place for this row.

Code:

row4.Power 

Using the data shown above with this method, the result received from the tLogRow is this......

1|1|Bob|27|12|0

2|1|Bob|27|1|-11

3|1|Bob|27|23|22

1|2|James|32|14|0

2|2|James|32|140|126

3|2|James|32|12|-128

1|3|Jane|42|23|0

2|3|Jane|42|29|6

3|3|Jane|42|32|3

The last column is the difference between the that row and the previous row.

[Alpha549]

Hello @Richard Hall​ 

Thank you for you answer.

Ok, so in my case with almost 70 columns, I must apply what you said for each !

It's a nice method to know !

[Anonymous]

You only need to carry out the logic of identifying the difference between the rows on the columns where you need to know the change. The rest you can just feed from the input table of the tMap to the output.

[Alpha549]

Understood rhall, thank you again ! 🙂