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?
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 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
