Alteryx to Knime - Tile Function

We have a portion of a predictive model (to estimate close rates of open opportunities) that uses the Tile and Running Total functions in Alteryx to calculate win rates based on a number of factors (product, source of opp, etc.). Essentially all won proposals are assigned a value of 1, and all lost proposals are assigned a 0. Proposals are ordered according to date created, and assigned a Tile_Num (which is based on the product type - i.e. all proposals for product A are assigned a “1”, all proposals for product B are assigned a “2”, etc.) and a Tile_SequenceNum (which is numeric based on the placement of the record within the “Tile”). The running total of “won” proposals is then compared to the tile sequence number of the product to produce a current win rate.

Is there an effective way to replicate this in Knime? See example below:

createdon Win_Status_Numeric Product Tile_Num Tile_SequenceNum RunTot_Status_Numeric Win%
10/2/2020 16:36 0 A 1 1 0 0
10/8/2020 16:28 0 A 1 2 0 0
10/15/2020 15:52 0 A 1 3 0 0
10/21/2020 18:22 0 A 1 4 0 0
10/22/2020 16:54 0 A 1 5 0 0
10/26/2020 17:17 0 A 1 6 0 0
10/26/2020 18:45 0 A 1 7 0 0
11/2/2020 21:55 0 A 1 8 0 0
11/3/2020 20:09 0 A 1 9 0 0
11/4/2020 17:19 1 A 1 10 1 0.1
11/4/2020 19:00 0 A 1 11 1 9.09E-02
11/4/2020 21:45 0 A 1 12 1 8.33E-02
11/4/2020 22:58 0 A 1 13 1 7.69E-02
11/5/2020 17:26 0 A 1 14 1 7.14E-02
11/5/2020 20:30 0 A 1 15 1 6.67E-02
11/9/2020 14:35 0 A 1 16 1 0.0625
11/9/2020 14:36 0 A 1 17 1 5.88E-02
11/9/2020 17:28 1 A 1 18 2 0.111111111
11/9/2020 17:55 0 A 1 19 2 0.105263158
11/10/2020 17:07 0 A 1 20 2 0.1

not sure if there is one node for everything but the RunTotS_Status looks like a Moving Aggregation Node with cumulative aggregation of Win_Status and if Product is sorted moving aggregation might also get Tile number bei Unique count (cumulative). Tile Sequence might require ranking node
Maybe @takbb has already build a component for that I am currently not aware.
There have been a few people transitioning from Alteryx recently so you also might check the forum for those topics
Anyway. You might have a look at mentioned nodes.Hope that helps


@Daniel_Weikert ! :rofl: :rofl:

@mclanea1 Welcome to the KNIME community. I agree with what has been said that the moving aggregation and ranking nodes would generally be used able to achieve this. It could also be achieved using Java Snippets and in fact Column Expressions. (possibly a sequence of column expressions… I’ll have to think about that and give it a try, or maybe somebody else will beat me to that solution!).

But since Daniel mentioned components, yes I do indeed have some components that can assist here. One component “First and Last for Group” marks the beginning and end of blocks, based on change of Product Code. This is then followed by the “Mark Blocks in Table” which allocates a sequential number to each block that has been found by the previous component, then the Rank node is used to sequentially number within the group, a further component “Cumulative Sum for Grouping” sums the “Win Status” within product/tile. The Moving Aggregator node can cumulatively sum, but there is a small complication in that the count must reset for each product.

Finally the Math Formula node can perform the calculation of Win% using the formula you described.

Here is a sample workflow

I put in the product/win status values from your example, and then added some extra product lines of my own.

Numbering and sequencing tiles.knwf (155.6 KB)

Here are the components

If you are new to KNIME, you may not have come across how components work. Components are simply a sequence of other KNIME nodes that have been packaged up and made available, a bit like “macros”. You can copy/paste a component from one workflow into another, or you can drag and drop the icon on one of the above pages directly onto your workflow and the component will be downloaded.


Thank you! This is incredibly helpful. I really appreciate it :slight_smile:

1 Like

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