How to define a loop for a timeseries threshold?

KNIME Analytics Platform
1

Dear Knime Experts, 

Could you please help me with the question below? During a week has already tried almost everything and finally gave up.

I have a table which should be properly grouped by a certain condition: if a promotion is split by a pricelist, then these rows should be grouped by Product Name/Discount/MIN(Promo Start Date)/MAX(Promo End Date).

The main issue is that i dont know how to define a loop for a threshold in this excercise.

I know how to do this in Excel (like =IF(C2=D1+1;E1;IF(C3=D2+1;E1+1;0))), however this is gonna be applied on a huge dataset on a daily basis.

RAW Table:

Promo Start Date Promo End Date Pricelist Start Date Pricelist End Date
01.07.2015 17.07.2015 20.06.2015 17.07.2015
18.07.2015 23.07.2015 18.07.2015 14.08.2015
24.07.2015 14.08.2015 18.07.2015 14.08.2015
15.08.2015 20.08.2015 15.08.2015 18.09.2015
21.10.2016 18.11.2016 15.10.2016 18.11.2016
19.11.2016 16.12.2016 19.11.2016 16.12.2016
17.12.2016 20.01.2017 17.12.2016 20.01.2017

Desired table is looked like:

Promo Start Date Promo End Date Pricelist Start Date Pricelist End Date Flag
01.07.2015 17.07.2015 20.06.2015 17.07.2015 1
18.07.2015 23.07.2015 18.07.2015 14.08.2015 1
24.07.2015 14.08.2015 18.07.2015 14.08.2015 2
15.08.2015 20.08.2015 15.08.2015 18.09.2015 2
21.10.2016 18.11.2016 15.10.2016 18.11.2016 3
19.11.2016 16.12.2016 19.11.2016 16.12.2016 3
17.12.2016 20.01.2017 17.12.2016 20.01.2017 3

Thank you in advance!

Mikhail

2

Hi Mikhail,

First of all, apologies for the late answer. 

 

I would suggest the following solution: 

1. Convert your dates (String type) to Date cells with a String to Date&Time node

2. Use the Rule Engine node to create the flag (e.g. $Promo Start Date$ > $Pricelist Start Date$ AND $Promo End Date$ = $Pricelist End Date$ => 0)

3. Use the Group Loop Start node to pass the records with a single flag per iteration

4. Use the Table Row To Variable node to create a flow variable with the flag value

5. Switch workflow branches in accordance with the flag value with the CASE Switch Data (Start) node

6. Use appropriate aggregation for each branch with the GroupBy node. 

7. End switch (CASE Switch Data (End)) and group loop (Loop End (Column Append)). 

 

Please find attached sample workflow. 

 

Best,

Anna