As a pilote project to introduce Knime in my company (the one i am working for, not MY company), i need to compare data from on database with 3 others.
I reached to get all the informations of all 4 databases in 1 table. So I have the same “block” of columns 4 times.
The Tennants are: “IS-E”, “RE_NETZ”, “RE_VERTRIEB” and “RE_HKN”.
So, I have a table with all 4 tennants next to each other, that’s great. But now I need to compare the values of the fields x.x.x.
I need to compare the IS-E-values with those of RE_NETZ. But: if RE_NETZ has no value for that meterpoint, I need to compare IS-E with RE_VERTRIEB, and when this one also has no value to compare it with RE_HKN.
At the end I need a new table with only 2 column-blocks like mentioned above. One with all the original IS-E values, and one with the RE_XXX values.
Has anyone an idea, how I can create that multiple comparison?
I already tried with Math Formula (Multi Column), I think this is the right extension, but I couldn’t figure out how I could configure it the way I need.
To avoid users guessing your exact structure perhaps you can share piece of your data (or if confidential dummy data is just good enough as real one as long it represents real one properly) and desired output of above mentioned comparison? From experience this approach works really well as users are able to quickly understand what is your task and offer their’s solutions.
Here is a view on the table, due to the large amount of columns, I couldn’t get all of them on the screenshot, but you can see the IS-E Block, the RE_NETZ Block and the beginning of the RE_VERTRIEB Block
the max_in_args has problems, when I have an empty field with “?”
I think I could eventually fix that in the SQL-SELECT at the beginning, to escape the “NULL”
the expression is done for every included column, but always (of course) the same result.
How can I dynamically create the expression for every combination I need? Or do I need to add multiple Math Formula Extensions in a row with each one included column?
Is Math Formula still the correct extension for my need, or is there any other one, maybe a little bit more flexible?
so by comparing you mean you want to find the max value or? Think you’ll need a loop and couple of more nodes here to get what you want. You can take this example which does table comparison and try to modify it to get what you need:
If you’ll have problems I can give a try to modify above workflow (or at least use it’s idea to do comparison) but would help if you can share some data and desired output. (Dummy data is more than ok).
If you first unpivot your 3 tables (e.g. having your 4 columns in 1 row for each of the tables) and then use column appender node to stack them together you should be able to subtract the values (e.g the rows). You could create an additional column with filled values for each empty cell in your second table and use this as the column to calculate the difference
Was that understandable?
BR
