Four Stars

## Find greatest value in column and update rows that match a criteria

Hi,

Let's say that i have a excel file containing this values:

 Product Id Discount Id Target Value1 Value2 PRODUCT1 ALL PRODUCT1 A SINGLE 0 11 PRODUCT1 B SINGLE 0 11 PRODUCT1 C SINGLE 1 5 PRODUCT1 D ALL PRODUCT2 D SINGLE 2 14 PRODUCT2 D ALL

What i need is to iterate over all rows for each product and find the greatest value in column "VALUE2" where target is "SINGLE" and then copy that value to "VALUE1" where target is "ALL". Please check the table bellow in red

The expected output should be :

 Product Id Discount Id Target Value1 Value2 PRODUCT1 ALL 11 PRODUCT1 A SINGLE 0 11 PRODUCT1 B SINGLE 0 11 PRODUCT1 C SINGLE 1 5 PRODUCT1 D ALL 11 PRODUCT2 D SINGLE 2 14 PRODUCT2 D ALL 14

Does anyone have any insights on how to achieve this scenario?

Five Stars

## Re: Find greatest value in column and update rows that match a criteria

Hi ,

Can you let me know more details on your requirement.  Where you will update the Column 'Value1"?

In the same file or in any db table?

i just want to know you need all remaining columns data as is? or you need to find max and update a single column?  This info helps!

Thanks,

Jay

Four Stars

## Re: Find greatest value in column and update rows that match a criteria

I'll be reading an excel file, doing some operations not relevant with a tMap. What i want to it iterate after having the tMap and write the result value to an excel output file again.
Basically i want to keep everything the same and just update the column Value1 of the rows where target is "ALL" with the greatest value for that product ID.

In my example :
product1 have two rows with target = "ALL" and the greatest value of value2 for that product is 11 so I'll update value 1 of row0 and row4 with 11.
for product 2 we have only one target = ALL so the value1 for that row should be 14

I hope that this helps you more

Five Stars

## Re: Find greatest value in column and update rows that match a criteria

Hi,

I have gone through your problem and tried out different approaches. I felt below solution is easy.

Try it once!!

main flow is reading your file and add a lookup flow with filter component to filter  "SINGLE" type records

and aggregate row to aggregate on product to find max value.join this lookup with main flow on product id  and in tmap add a expression like row1.Target.equals("ALL")? row4.value2.toString() : row1.Value1  . If record type is  "ALL", we will place max value of value2 coming from lookup else value1 from main flow.

providing screenshot.

Hope this helps!! let me know if you see any issues

Thanks,

Jaya

## OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

## An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

## Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

## Getting Started with APIs

Find out how to get started with APIs