Filtered column update

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


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?

Does this work

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

I think this solves it:

Bob.transform (4.1 KB)

1 Like

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”,(C31.1),IF(B3=“Mat 2”,(C31.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

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

1 Like


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

Transform file.
FilteredColumnUpdate.transform (2.1 KB)

1 Like