I have two text files Data1 and Data2 wade.txt (1.8 KB) test1.txt (1.5 KB)
Data1 has 9 columns and Data2 has 6 columns
I need to add Column7 and Column8 to Data2 on below given condition
The 3 conditions are
A. column3 (MRC) of Data2 should be multiplied with column4 (TAX_RATE) of Data1 if column3 (TAX_TYPE) of Data1 matches to column4 (TAX) of Data2. (to be added to column7 of data2)
B. If the column4 (TAX_RATE) is 0 in data1 then do not multiply with MRC of data2 but pass the value of column5 (TAX_FIXED_AMOUNT) of data1 to Column7 of Data2
C If column7 (after adding to data2) of data2 does match with column6 (Percent) of data2 then pass TRUE to column8 in data2.
But,
If it does not match then add the value of FFX (which is present in (column TaxAmount) in data2) to MRC(data2) and multiply the result (i.e FFX+MRC) with the value of TAX_RATE (data1). (by doing this the result should match to column6 (Percent) of data2) and as it is matching now so we should pass True to column8 in data2 or else False
Read both data into string variables and then use generate datatableactivity and convert into datatable…
Then use for loop on the base table for your case data2…then inside that you can use filter on data 1 based on the columns you want to compare…then use if conditions to check if you got any row after filter or not…if yes then perform the calculations as you need…if no then do nothing…like this add all the if conditions
Please try the same as above and let us know if you face any issues
Create a template Excel file that does the conditions / calculations.
Read the txt files to datatables, then paste the datatables to your Excel file and let it do the calculations. Then you can read the Excel file result to a datatable, and save as txt.
These calculations will be much easier to develop, troubleshoot, and change if you build them in Excel.