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”,(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 |
| …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).
2 Likes
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
Hi,
Here is my solution, you can add your additional conditions in the Javascript transformation easily.
Transform file.
FilteredColumnUpdate.transform (2.1 KB)
1 Like