# How to define a loop for a timeseries threshold?

Dear Knime Experts,

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

Mikhail

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)).