connect date with periode

Hi,

I am stuck in a problem for some days now, maybe somebody here can help me.

I have two tables, one that has the promotion data like this:

PROMO PRODUCT ; DATE BEGIN PROMO ; DATE END PROMO ; QUANTITY ORDER
XXX ; 2018-03-30 ; 2018-04-08 ; 120
YYY ;

And other one that has the sales data by day:
DAY ; PRODUCT ; SALES
2018-03-28 ; XXX ; 10
2018-03-29 ; XXX ; 15
2018-03-30 ; XXX ; 5
2018-03-31 ; XXX ; 7
2018-04-01 ; XXX ; 10
2018-03-28 ; YYY ; 10
2018-03-29 ; YYY ; 15
2018-03-30 ; YYY ; 5
2018-03-31 ; YYY ; 7
2018-04-01 ; YYY ; 10

Basiclly I want to know the quantity sold in the promotion periode and the difference between the orders and the sales. Some how i have to conect the product and the dates from the two tables (the joiner node does not work because in one of the tables I just have the first and the last day).

Ideas please?

Hi

Here is the workflow to do the task:

I looped over promotions and add the promotion ID (the row ID for each promotion) to the sales table by using this expression in Column Expressions node:
if (column("prod") == variable("prod") && (column("Date") >= variable("Date_Start") && column("Date") <= variable("Date_End"))) variable("RowID")
Then joined the tables on the promotion IDs, GroupBy to calculate the sum of sales and the Math Formula to calculate the difference between orders and sales.

Take a look at the workflow and let me know if it’s working as expected:
promo-sales.knwf (38.3 KB)

Best,
Armin

1 Like

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