find change within a column and give indication

KNIME Analytics Platform
1

Hi all,

I struggle with this for a while. I want to create the columns “where change occurs” , to indicate the change of a product and then afterwards to look on the DATES column ad note the MIN and MAX for each “Where change occurs” group.

CUSTOMER DATES PRODUCT WHERE CHANGE OCCURS MIN and MAX
04fa02d37db 2014-02-07T06:56:38 PRODUCT A 0 MIN
04fa02d37db 2014-02-10T11:27:58 PRODUCT A 0
04fa02d37db 2014-02-10T11:31:10 PRODUCT A 0
04fa02d37db 2014-02-11T06:07:28 PRODUCT A 0 MAX
04fa02d37db 2014-03-06T07:01:04 PRODUCT B 1 MIN
04fa02d37db 2014-03-06T07:18:21 PRODUCT B 1
04fa02d37db 2014-03-06T07:38:56 PRODUCT B 1
04fa02d37db 2014-03-11T07:21:48 PRODUCT B 1 MAX
04fa02d37db 2014-03-18T07:30:08 PRODUCT C 2 MIN
04fa02d37db 2014-03-25T06:37:16 PRODUCT C 2
04fa02d37db 2016-04-06T08:09:49 PRODUCT C 2 MAX
2

Offhand I’d say sort by date, then GroupBy on PRODUCT using First and Last aggregation. (You might need separate branches for the first and last aggregations, which you could then concatenate back together.)

EDIT: Wait, I realized I didn’t really answer your question, since you’re not just trying to identify first and last, but instead populate the WHERE CHANGE OCCURS column. I will think more. Maybe a Lag Column is needed combined with a Rule Engine.

3

Hi Scott if you have a good idea on how to get to this, It will be great if you attach a workflow example - including the calculation per node

4

I’ll see if I can pull something together, but it may take time. Maybe someone else can jump in - or has a better approach altogether.

5

@11atzitzi

Here’s something that works. It could better documented (and probably simplified) but hopefully it will help you.

2019-03-20%2010_00_55-KNIME%20Analytics%20Platform

ColumnChangeIndexExample.knwf (33.0 KB)

6

Many thanks. it works perfect.

1 Like
7

Hi there!

You can also use GroupBy node with First and Last aggregation methods on Dates column. Or Max and Min if that is when the change occurs :wink:

Max%26MinWithinGroup
Max%26MinWithinGroup774×293 9.1 KB

2019_04_03_ColumnChangeIndexExample_ipazin.knwf (23.8 KB)

Br,
Ivan

8

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