Bring latests cost to a sales spreadsheet

Hi everyone! Hope you’re enjoying your weekend.
I’m struggling with something I believe should be easy to achieve.

SITUATION: I have a spreadsheet with a list of sales (date, product, subproduct, price) and I want to bring as a new column, what was the price at the time the sale was done

WHAT DO I HAVE:
1) Sold products: List of all products sold - note that some products (e.g., socks) don’t have a subproduct variation (e.g., color)
image

2) List of costs: As you can see, the same product shows up more than once. That happens when a supplier updates the cost. For example, the cost for the T-Shirts was $6 from 1/1/23 up until 1/14/23, when it was updated to $9
image

3) Desired output: Add two columns to the original spreadsheet. In the first column, bring the cost of the product-subproduct combination that applies to the time of sale (should be the closest price available in the past). In the second column, just add a flag if the product wasn’t found in the cost database.
image

Any help or suggestion is HIGHLY HIGHLY appreciated.

Thanks you all!
DR

@damianrudich Could you please upload the example data?

Thanks so much. See attached!

Appreciated :100: :100:

Sales.xlsx (9.1 KB)
Costs.xlsx (8.9 KB)

hi @damianrudich ,
this is my approach.
KNIME_sales-cost-MM.knwf (128.9 KB)
image
hope you are enjoying using knime. welcome to the community.
happy knimining.

rgds

4 Likes

Very nice. I developed a workflow, but its not nearly as simple and efficient as yours. Kudos.

1 Like

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