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.

[Anonymous]

Can you show us what you are doing. If your Excel schemas are the same, the tUnite should work for this.

[Keihilin]

Hello Rhall,

Sure, in the graph below, I'm trying to link (main) the 2nd DATALAKE_XLSX file to the tUnite_1 component, but it's not allowing me to do that.

[Anonymous]

This is because of the OnSubJobOk links you have. You can only have one "start" component in a SubJob. This component can have multiple OnSubJobOk links, but you cannot have more than one "start" component. Just move the tFileList_2 and 4 to be triggered somewhere else.

[Keihilin]

Hello Rhall,

thank you, I can now connect both Excel file.

But I think I expected another thing, the possibility to select what to merge..

As, I would like to keep the lowest value, when the ItemID is present in both file.

I guess, the logic should be to be set on the Tmap ?

[Anonymous]

The tUnite just combines the two data sources in to one data source. If you need to keep the lowest value when the ItemID is present in both files, you should join using a tMap and then use a tAggregateRow to group by the ItemID and return only the lowest value for the ItemID.

[Keihilin]

Hello Rhall,

Could you please show me via a screenshot of what I should get as structure?

If I try to link both Excel to a Tmap, the First one is correctly connecter (Main) the 2nd is only in "lookup".

[Anonymous]

If you only need to worry about records where there is a matching ItemID, then adding the second file as a lookup should be fine. Or have I misunderstood the requirement?

[Keihilin]

Ok, so I've 2 files, let's say Report A and Report B

in Report A I've 40 000 items, (with ID and a INT Value)

in Report B I've 10 000 items (with ID and INT Value)

some of ID are in both files, but Report A contain ID not present in Report B, and Report B contains some ID not in Report A.

What I need is to load in the Database All ID, but only 1 Time for duplicate ID. and the logic is to load only the lowest INT value.

Hope I was clearer ?

[Anonymous]

In which case, a tUnite will work. Unite the data into one data set and then use a tAggregateRow to group by ID. With the tAggregateRow's operations table, create one record for the INT value and set the function to MIN.