Getting Similar results from Alteryx Multi-Row tool using KNIME Expression

KNIME Analytics Platform
1

I’ve been struggling to replicate a result using the Alteryx Multi-Row Formula Tool with grouping enabled. After extensive trial and error, I’ve managed to get about 90% of the records to match. However, there are still some key discrepancies—specifically, cases where the result is 0 instead of empty (or vice versa), or where values are reversed. These inconsistencies are causing errors to cascade into subsequent rows.

Below, I’ve included the logic used in the Alteryx tool, along with the corresponding expression in KNIME (version 5.4.4). I’ve also attached a workflow that includes a sample of the source data, the resulting field from Alteryx (End Inv), and a join that compares the results side by side.

To emulate the configuration options in Alteryx, I’ve added extra steps for sorting and used a Group Loop in KNIME. However, I’m not confident that I’ve implemented this correctly—or if there might be a simpler or more robust approach, possibly using an existing KNIME component that someone has already developed.

Alteryx Logic:

if IsNull([Row-1:Sales]) or [Row-1:Sales]=0
then
(if [Row-1:Gross Inventory] - [Sales] > 0
then [Row-1:Gross Inventory] - [Sales]
else [Row-1:Gross Inventory]/[Sales]
endif)
else 0
endif

KNIME Expression that is partially working:

if (
($[“Sales”, -1] == MISSING) or ($[“Sales”, -1] == 0),
if (
($[“Gross Inventory”, -1] == MISSING) or ($[“Sales”] == MISSING),
MISSING,
if (
($[“Gross Inventory”, -1] - $[“Sales”]) > 0,
$[“Gross Inventory”, -1] - $[“Sales”],
if (
($[“Sales”] != 0),
$[“Gross Inventory”, -1] / $[“Sales”],
MISSING
)
)
),
0
)

I used a field RecordID in Alteryx to help do this comparison.

Thanks for anyone that can review and provide guidance or suggestions.
Multi-row_Alteryx_KnimeMigration_example.knwf (158 KB)

1 Like
2

Dear aehrenworth,

I did my best I simply could not understand the logic behind this bold line, (if you dont mind) my real question is: why are we dividing?

if IsNull([Row-1:Sales]) or [Row-1:Sales]=0
then
(if [Row-1:Gross Inventory] - [Sales] > 0
then [Row-1:Gross Inventory] - [Sales]
else [Row-1:Gross Inventory]/[Sales]
endif)
else 0
endif

Scenario 1:

Row-1:Sales = NULL, Row-1:Inventory = 100, Sales = 40

  • Condition: Row-1:Sales is NULL or 0 → True
  • Check: Row-1:Inventory - Sales > 0 → 100 - 40 = 60 → True
    Result: 100 - 40 = 60

Scenario 2:

Row-1:Sales = 0, Row-1:Inventory = 30, Sales = 50

  • Condition: Row-1:Sales is NULL or 0 → True
  • Check: Row-1:Inventory - Sales > 0 → 30 - 50 = -20 → False
    Result: `30 / 50 = 0.6 (Is this supposed to be a ratio? Unclear.)

Scenario 3:

Row-1:Sales = 100, Row-1:Inventory = 500, Sales = 50

  • Condition: Row-1:Sales is NULL or 0 → False
    Result: 0

Is that the correct logic and examples you wanted?

According to your answer, I will dive into it.

Best,

Alpay Zeybek

1 Like
3

I didn’t write the original workflow in Alteryx. I was simply trying to help port it over to KNIME for a colleague.

I’ll see if I can connect with the business team that came up with the logic and understand why they’re dividing, and get back to you.

Thanks for your help so far.

1 Like
4

Thanks again for breaking it down this way… very helpful to try and look at each step, especially since it is not my process/data.

I connected with the other colleague and for now our best guess is the divide logic was meant as a catch for when Sales Row -1 is 0 and Gross Inventory -1 is also 0 to return a value of 0. It only accounts of 84 rows out of 47k

Overwhelmingly in the Alteryx flow the result is either null or 0 (45k)

only about 1400 or so rows are doing a subtraction calculation when Sales Row - 1 is 0 Gross Inventory Row -1 is NOT 0. Then the End Inv value returns Gross Inventory Row - 1 minus Sales (the current row). Screenshot sample for one of these.

image
image847×82 3.98 KB

I think the main issue might be sorting of the data in KNIME and making sure it stays the same in the Group Loop (but that is just a guess).

1 Like
5

Hi aehrenworth,

I have applied java snippet node and used your logic without any loop.
Can you please check the revised flow?

Best,

Alpay

Multi-row_Alteryx_KnimeMigration_example.knwf (105.6 KB)

2 Likes
6

Thank you for sharing this for testing. I noticed two things:

  1. The Java snippet is showing an error message: “Exceptions in the code caused missing rows to be output. Check log for details.” I assume this is a common issue and not the cause of the second point.
  2. With this new Java snippet, there are actually a few more mismatches. Most of them involve cases where the KNIME output includes a total, while the Alteryx output shows either zero or null—and vice versa. There are no instances where both outputs have non-zero values that differ.

I included the “End Inv” value from the original dataset to help with comparison. Not sure if this is easily resolvable, especially if Alteryx inherently handles certain value types differently.

1 Like
7

Hi aehrenworth,

Just to clarify, in my current KNIME setup, I had applied a Missing Value node to convert missing (null) values to 0 for easier downstream processing. That might actually be the reason why KNIME is returning values in cases where Alteryx shows null or nothing at all.

Would it make more sense if you leave the missing values as-is and re-run the logic without filling them with zeros, so that we can get a cleaner 1:1 comparison with Alteryx?

Let me know what you think—happy to revise accordingly.

And, I thought our goal was to compare the End Inv values generated in Alteryx with those calculated using the KNIME-based alternative. It seems I may be missing a key part of the logic here—could you please elaborate again when you have a moment?

Thanks in advance,

Alpay

8

Ok, I think I am following now- I thought the Missing Value node was a validation piece you added.

I will try to review again this afternoon but may have to wait until after the 4th of July holiday.

If I am still stuck or seeing differences I will reach out next week.

Thanks again!

2 Likes
9

Okay, we’re very close now. I’ve aligned everything with your Java snippet, and it’s working as expected. The key was avoiding the use of Group Loop, which appears to alter the original data’s order.

Since these calculations are highly dependent on row order, it seems that the Group feature in Alteryx Multi-Row behaves fundamentally differently from Group Loop. For now, I’m marking your solution as correct. However, I think we still need to determine the original data’s sort order to ensure it’s clearly defined before using the snippet.

1 Like
10

Hi,

Glad to hear that! I didn’t use any loops—instead, I relied on the data order you provided in the first node to replicate the output from Alteryx.
Please feel free to reach out if you need any help.

Best regards,

Alpay Zeybek

11

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