Help with FOR or WHILE Loop in Column Expressions Node

Hello KNIMERs,

I have the following situation with returned inventory:

image

  1. For a given Category, and Item, a negative Amount is entered. Many returns are possible.
  2. These negative returns also show IsConsumed = ‘0’ to indicate this amount is not used in final production.
  3. The goal is to remove a SINGLE matching positive record for each negative return.
  4. The end result should be only 1 line per legitimate IsConsumed = 1. In this case, I only want the yellow OR green boxes to flow through.

I am using the Column Expressions Node with the code below to create a new column Overwrite that should show ‘1’ if IsConsumed == ‘0’ AND it’s corresponding IsConsumed == ‘1’ line.

image

Here is the output I currently get:

image

Here is the output I want after using a Row Filter to remove OverWrite = ‘1’:

image

I suspect that I need a FOR or WHILE loop to iterate over each Item, but I am not sure.

I am attaching a sample workflow with dummy data. Any help would really be appreciated!

I’m not certain that I understand everything about what you’re trying to do. This workflow produces the desired outcome from your post. Let me know if I’ve misunderstood something.

1 Like

@rfeigel, thank you for your help!

I like what you did with the chunk loop, but it doesn’t work if the number of returns increases.

Basically, what I am trying to do is to have 1 negative line cancel out 1 positive line as long as the Item# matches.

The output should only show positive lines that remain.

I thought this would be pretty simple to do in the Column Expressions node.

Here is the pseudocode:

image

Create new constant value column “Overwrite” == 0

FOR a given [Category#] and [Item],

IF “IsConsumed” = 0
THEN Overwrite = 1,

Look for 1 row where Abs(“Amount”) = “Amount” ##to match the negative and positive amounts.

IF TRUE, Overwrite = 1, ELSE Overwrite = 0

Finally, filter every row where Overwrite = 1, and you’re left with the remaining valid rows.

The final output should look like the below image where only the last two lines pass the filter with Overwrite = 0.

image

I’ll keep plugging away at it, but any help would be greatly appreciated! Thank you!

Hi @Haystack ,

I wasn’t sure about the purpose of IsConsumed and if it makes a difference to the “matching” of negatives to positives, but the rest of the matching can be performed I think without a loop, by splitting the negatives from positives then assigning a rank value to each item / absolute amount combination, and then joining to discard the matches


Inventory Returns - 2.knwf (91.0 KB)

2 Likes

Hi @takbb, this worked! I never would have thought to use the rank nodes…very clean solution.

Still curious if a loop or a for statement would have worked.

Regardless, thank you very much for your help!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.