Hello all -
I wonder if anyone can assist. I have a table of products and their price over time (Table A):
| TABLE A | | | |
| Product ID | Valid From | Valid To | Price |
| 1234 | 01.01.2021 | 30.04.2021 | 3.00 |
| 1234 | 01.05.2021 | 30.08.2021 | 3.50 |
| 1234 | 01.09.2021 | 31.12.2021 | 3.75 |
| 1342 | 01.01.2021 | 30.04.2021 | 5.00 |
| 1342 | 01.05.2021 | 30.08.2021 | 5.25 |
| 1342 | 01.09.2021 | 31.12.2021 | 6.00 |
| 2331 | 01.01.2021 | 30.04.2021 | 4.33 |
| 2331 | 01.05.2021 | 30.08.2021 | 4.52 |
| 2331 | 01.09.2021 | 31.12.2021 | 4.61 |
| 2331 | 01.01.2022 | 31.12.9999 | 4.88 |
I then have a separate data source of product sales (Table B):
| TABLE B | |
| Created Date | Product ID |
| 01.04.2021 | 1234 |
| 17.09.2021 | 1342 |
| 17.09.2021 | 1234 |
| 20.10.2021 | 2331 |
| 03.11.2021 | 1342 |
| 25.12.2021 | 1234 |
| 07.01.2022 | 2331 |
What I need is a workflow that will look up the Created Date in the Valid To/From range and the Product ID from Table A and append the relevant Price to Table B. The desired outcome should look like this:
| DESIRED RESULT | |
| Created Date | Product ID | Appended Value |
| 01.04.2021 | 1234 | 3.00 |
| 17.09.2021 | 1342 | 6.00 |
| 17.09.2021 | 1234 | 3.75 |
| 20.10.2021 | 2331 | 4.61 |
| 03.11.2021 | 1342 | 6.00 |
| 25.12.2021 | 1234 | 3.75 |
| 07.01.2022 | 2331 | 4.88 |
It's probably really simple but I'm fairly new to this and Google searches have gotten me nowhere - any help would be gratefully received.
Many thanks
RDF
