Recreate Multi-Row result from Alteryx in KNIME

KNIME Analytics Platform
#1

Hi,

I’m new to KNIME and are trying to convert an Alteryx workflow.

In my input file I have a list of materials and a creation date.

The materials can occur several times in the list but have a unique creation date.

I want to create a column ‘End Date’ based on Material and the next Creation Date in the list.

If the material only occurs once or if it is the last occurrence of the material in the list, the end date should be NULL.
image

So, for material 000000000017550001 in the example above it has it’s first creation date 2015-01-26.

The next creation date for the same material is 2015-02-18.

And since this material occurs three times in the list it has a third creation date 2019-11-11.

End date shall be the next creation date for the same material minus 1 day (to have a start and end date for the period)

The NULL values will be replaced by today’s date later in the workflow since they are still valid periods.

In Alteryx it is easily done with the Multi-Row formula:
image

But I haven’t found any node that can do the same in KNIME.

Do you have any suggestions on how to solve this?

BR

Andreas

multi row formula
#2

Hi @wumfwu

You can approach this in several ways depending on your preferences like code vs no-code. One option is via a Column Expression using the new column() features:

if (column("material").equals(column("material",1))) {
    column("creation",1)
} else {
   null
}

It checks if the material number of the current row is equal to the material number of the following row. If so, take the creation date of the following row. If not, leave it empty.

image
image1126×715 29.7 KB

Make sure you enable the multi-row access under the Advanced tab and Use null for prior rows

image
image761×701 15.3 KB

Next, you can shift the date by 1 with the Date&Time Shift node. Use -P1D as Duration Value.

image
image1277×903 37.8 KB

Hope this provides some inspiration!

Tip: try to include your data in a workable format to avoid people having to manually type over your data table from a screenshot. People are more inclined to jump in if you make their life easy as well :wink:

7 Likes
#3

Hi, thanks a lot this looks really promising.

Your tip is noted…thanks!

BR
Andreas

1 Like