Use an compare 2 excel file to update 1 database table

[Keihilin]

Hello,

I need to update a table with information coming from 2 excel files, both excel have same structure.

in each excel, I've an itemID (the same ID can be in both excel)

and for each ID a value INT in another column

I need to map the lowest INT value for each itemID.

I can't connect both Excel to the Tmap, but I found a component called "tUnite" but I can connect "merge order 1" only with the first excel.

the second do not allow the connection.

What am I doing wrong ? or maybe there is another component to allow comparison et flow the data to the tmap ?

Thank you.

[Keihilin]

Hello,

This is the screenshot of my tAggregateRow item:

so, in "Group By" I put the ID (column A) and all the value that I want to keep (H, I and source)

then, in "Operations" I select the "Value" to be checked (column J), I select the Function "min" and set an output column "lowervalue". Also ignore the Null values.

When I run the script, I have no errors, but I checked the end result, and it's not the lowest value that I see in my table..

Could you please let me know what am I doing wrong?

[Anonymous]

The values that you have in the group by, are they all part of the same group? For example, the data below will always return 2 rows, because the values in A, H, I and Source are only different for 2 of the 3 rows ....

A-------H------I------Source--------J------

1-------2------3------Hello---------1------

1-------3------4------Goodbye-----1------

1-------2------3------Hello---------1------

If A is the key and the only thing that you want to group by, then only A should be in the group by. In this case, you would only get 1 rows returned with A = 1 and J = 1.

If you need to return all of the data, you will need to know which of the values from H, I and Source you want (the MAX, MIN, FIRST, LAST, etc).

[Keihilin]

Hello,

That's an example:

A---------H---------------I------------SOURCE-------J

1------2/3/2022-----3/2/2022-----Source1-------45

1------6/8/2019-----7/8/2019-----Source2-------847

Then, I would like to get at the end:

1------2/3/2022-----3/2/2022-----Source1-------45

the only calculation is on the J value (min)

but once selected, I need to keep all the value of the other field.

is that possible?

[Anonymous]

Yes. Group only by column A and add the other columns like this in the operations box....

H = MAX

I = MAX

SOURCE = MIN

J = MIN

[Keihilin]

Sorry my example is maybe not so good.

as the value of H, I and SOURCE are not always like:

H = MAX

I = MAX

SOURCE = MIN

Actually Source is a string (no int value) to inform from which file the info comes.

and the date H and I can be sometimes MAX or MIN.

the only sure thing, is to group on A and pick the whole row with the J = MIN