I’d like to get help with the following problem I have.
I have a list of different products, on that list I have the price validity dates, for 1 product I can have more than 1 line since every time the price changes a new line is created with the new price and the new validity dates.
What I need to do is to check for each product all the prices and take the 2 most recent lines to compare them and see if the new price changed vs the old one or not.
In the example attached my knime will need to take out the lines with Valid From 2/7/2018 & 7/30/2018 which are the most recent ones. How can I do this?
Currently I use a groupby with filter by date max to remove all the duplicates and use only the most recent dates price, however now that I need to have 2 lines per product I’m not sure how to proceed.
Use a Group Loop Start node with the Product column as the grouping column. Then in the loop body, use a Sorter to sort by Date column in Descending order, then Row filter, keeping the 1st two rows only. Now you have the two prices for the product and you can compare them various ways, e.g. Groupby, with Min and Max aggregations, then use a Java Snippet with a boolean output column with the result set by Double.compare(Min,Max)==0 (will be ‘true’ when the price is unchanged), where Min and Max are the relevant price columns.
Make left join of you products table to itself by Product ID.
Sort result by date (say valid to) descending and same field from second table (say valid to 1).
Delete records where valid date = valid date 1 for the same product.
Use group by with max as you currently doing to join to the new table (with left join) and use only new table fields as output.
Compare price and price 1 using one of the filter node.
Thanks @s.roughley and @izaychik63 for your help on this. I went with s,Roughley’s suggestion which worked great to get only the 2 most recent dates for each code.
For the other part of getting the value if the price changed I used a rule engine because I do other checks than just the intial comparisson of both pricess.
