How to handle complex calculation

What should be the syntax of if condition

input1.txt
Group, Amount, Rate, Type, value, type2, result
1/2,abc,10,0.4,xyz,1.4
1/2,bbc,10,0.5,abc,1.6,xyz
1/2,ddd,10,0.6,xyz,1.7
1/3,abc,10,0.4,xyz,1.4
1/3,bbc,10,0.5,abc,1.6,xyz

input2.txt
Column1,Column2
abc,0.4
bbc,0.5
ddd,0.6
xyz,0.7

What I am trying to achieve is

If 1/2 = bbc and if type2 is xyz then multiply the column “value” of abc (1.4)(input1) with column2 of xyz (input2)

But if 1/3 = bbc and if type2 is xyz then multiply the column “amount” of bbc (10)(input1) with column2 of xyz (input2)

Can anyone help

does 1/2,abc these belong to same column or different …please elobrate your requirement

1/2 belongs to column Group in input1
Abc belongs to column Amount in input1

But calculation is happening with the value of abc which is 1.4 (under the “Value” column) in input1

Can anyone help please

You would have to do a for each row to work out which ones meet the requirements,

copy these to another data table

Effectively giving you one table of 1/2 and a table of 1/3

Loop the 2 tables (1/2,1/3)

looking up the required values from input 2 adding them to new columns then performing the calculation

combine the 2 tables back together

Thank you for helping but I am not sure how to reproduce this process

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.