What I would like to do is create a new column with updated values, as below

Bob

The input and the output are mixed together here, which makes it a bit confusing. Can you show the input and the desired output(s) separately?

Iâ€™m still confused.

Is this the input?:

And these are the 2 outputs?

Are they to be output to:

- different CSV files?
- the same sheet of an Excel file?
- a different sheet of the same Excel file?
- a different Excel file

The yellow panel is the base table, the original information, the green panel is a new column which has been created next to the original information and will be populated with data created by recognizing certain conditions in column A or B (Cust 1 or Mat 2) and changed by factors in another table or somewhere.

I think the excel function could be a if statement (nested of course)

=IF(A3=â€ścust 1â€ť,(C3*1.1),IF(B3=â€śMat 2â€ť,(C3*1.2),C3)) and if both conditions apply then more complicated.

This will create a new table of 4 columns to be exported to one excel sheet

let me know

Olaf Hi Thanks for the reply, it does solve the simple example shown and appreciate your time.

I concern is the scalability of the solution, say I have 100 different blocks for column A and 20 for column b the additional number of columns could be unmanageable.

If you have the time, any thoughts. Thanks again

You could create a lookup table for the multiplication factor and use the **Lookup** transform.

Key | Value |
---|---|

Cust 1;Mat 1 | 1.1 |

Cust 1;Mat 2 | 1.32 |

â€¦etc |

Then use **Concat cols** to create the lookup key (e.g. â€śCust 1;Mat 1â€ť) and **Lookup** to lookup the value.

You can use Easy Data Transform to create the lookup table and export it to file, e.g. as a CSV file. The **Cross** transform will create all possible combinations of 2 sets of values (cartesian product).

Complex conditions will result in complex implementation, indepent from programming language or tool. It will be a cascade of if then else. Limitation in EDT is that inside of If transformation calculation is not supported. I donâ€™t see any simple approach

Thanks Olaf I think admin have a solution

Sounds good thanks I will try it out

Hi,

Here is my solution, you can add your additional conditions in the **Javascript** transformation easily.

Transform file.

FilteredColumnUpdate.transform (2.1 KB)