Hi!! Knime Experts!!
Hope You are doing well.
I am facing issue for selecting appropriate price for given date for required Material Number.
Input-1 Material Number & Pricing date
| Material Number | Pricing date |
|---|---|
| 11478 | 01-12-2019 |
| 11478 | 01-01-2019 |
| 11479 | 01-01-2019 |
| 11479 | 01-12-2019 |
Input-2 Material Number, Price & Date range master data
| Number | Price | valid from | valid to |
|---|---|---|---|
| 11478 | 52 | 01-01-2018 | 31-07-2018 |
| 11478 | 53 | 01-08-2018 | 31-12-2018 |
| 11478 | 54 | 01-01-2019 | 30-06-2019 |
| 11478 | 55 | 01-07-2019 | 31-12-2019 |
| 11478 | 56 | 01-01-2020 | 30-06-2019 |
| 11479 | 101 | 01-07-2019 | 31-12-2019 |
| 11479 | 102 | 01-01-2020 | 30-06-2019 |
| 11479 | 103 | 01-07-2019 | 31-12-2019 |
Required Output:
| Number | Pricing date | Price |
|---|---|---|
| 11478 | 01-12-2019 | 55 |
| 11478 | 01-01-2019 | 54 |
| 11479 | 01-01-2019 | Record not found |
| 11479 | 01-12-2019 | 103 |
Explanation:
If for material no-11478 pricing date is 01-12-2019 & it has 5 price but validity range is different, so we need to select correct range i.e. valid from -01-07-2019 valid till -31-12-2019 and price is 55. & if price is not found give us “Record not found”
Date format- DD-MM-YYYY