Receive a 0 when the order number is a sequential number.

[StenAlferink]

Good day, I hope someone can help me with the following: when I have my file read by an Excel Reader and then have it written by an Excel Writer, I get the following.

As shown below, I would like to have a ‘0’ added to the ‘Paid’ column when there is a decimal point in the order number, for instance, as depicted.

When there is no decimal number following the order number, I would like to keep what is currently in place. Can someone assist me with this?

Best regards,
Sten Alferink

[Daniel_Weikert]

Hi
you could try rule engine node to first search for the “.” in the order column to identify the relevant rows, do a row splitter, use missing value fill for the relevant split and concatenate back together.
I am sure there is a shorter way but right now that’s what I could come up with as help.
br

[takbb]

@StenAlferink , following up on @Daniel_Weikert’s suggestion which I think would do the job, if it’s just a case of filling the missing values with a zero then the Rule Engine alone should be able to handle it I think. (Something like this. It could also use MATCHES instead of LIKE if regex rather than wildcards is preferred)

$Ordernr.$ LIKE "*.*" AND MISSING $Paid$ => 0
TRUE => $Paid$

and have it replace the Paid column

[StenAlferink]

Hello guys,

Thank you both very much for the help! Your explanation helped me to replace the empty values with 0 which is nice. I was wondering though is something like here is possible to do in KNIME:

So, column A-B is how I have the data and column F-G is what I try to achieve.
Just for a bit of context the numbers in de Ordernr. column are ordernumbers.shipments. If an order doesn’t have an amount than it should be empty, if the order does have an amount for example in the first shipment then the value of the extra shipments should be 0.

Do you think that is possible to achieve this?

[takbb]

Hi @StenAlferink , yes it should be possible by splitting the Ordernr column into two parts (order number and shipment). Then, a rule based row splitter could split out those orders that have a Paid value, and a lookup/join performed to determine which of the orders already have a prior Paid value, and then determine which shipments should then have Paid set to zero or left as missing.

There may be different ways to achieve the above depending on your version of KNIME. Which version are you using?

[StenAlferink]

Thanks for your response i use Knime 5.1.2 is this possible with this version?

[takbb]

Hi @StenAlferink , yes with KNIME 5.1 there are some new nodes which can be of use sometimes.

See if this does what you are looking for:
Update Column based on other criteria.knwf (86.3 KB)

This creates two columns Order Number and Shipment from the single OrderNr column. It then divides up the data set, firstly between those containing both a shipment number and Paid value, and then divides the remainder between those that have a shipment number and those that don’t.

Those with a shipment number and Paid value are used as a lookup table to be applied to those that have a shipment number but not Paid value:

Here I have used the new Value Lookup node, but it isn’t actually bringing a value in which is what it is normally used for, but instead simply returning an indicator true/false of whether a match was found. This is then used by the Rule Engine to set the value to zero.

After that, all the rows that were previously divided into separate groups are reassembled into a single table with the concatenate node.

becomes

and btw, welcome to the KNIME Community!

[StenAlferink]

Thanks @takbb this solution worked thank you very much!

[takbb]

HI @StenAlferink , glad it worked for you, and thank you for confirming the solution.