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 IdDiscount IdTargetValue1 Value2
PRODUCT1 ALL  
PRODUCT1ASINGLE011
PRODUCT1BSINGLE011
PRODUCT1CSINGLE15
PRODUCT1DALL  
PRODUCT2DSINGLE214
PRODUCT2DALL  


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 IdDiscount IdTargetValue1 Value2
PRODUCT1 ALL11 
PRODUCT1ASINGLE011
PRODUCT1BSINGLE011
PRODUCT1CSINGLE15
PRODUCT1DALL11 
PRODUCT2DSINGLE214
PRODUCT2DALL14 

 

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

3 REPLIES
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