Dear all,
i have a problem joining two data sets on the basis of a specific date within a date range. Additionally, another condition must be met.
I have various transaction records of certain products that refer to a certain price list at a specific point in time. At first it is important that the correct price list is applied, secondly, the transaction date must lie between the dates of the price list valid at this point in time.
Please find the first table below:
Transaction date | ProductID | Product | Pricelist |
01.01.2020 | 100 | ABC | X1 |
05.01.2020 | 101 | CBA | X2 |
01.03.2020 | 100 | ABC | X1 |
08.12.2020 | 100 | ABC | X1 |
10.12.2020 | 101 | CBA | X2 |
Table two:
Pricelist | ProductID | Product | Price | Valid from | Valid until |
X1 | 100 | ABC | 10 | 01.01.2020 | 20.02.2020 |
X1 | 100 | ABC | 12 | 21.02.2020 | 01.12.2020 |
X1 | 100 | ABC | 14 | 02.12.2020 | 01.01.9999 |
X2 | 101 | CBA | 100 | 01.01.2020 | 01.06.2020 |
X2 | 101 | CBA | 110 | 02.06.2020 | 01.01.9999 |
Result on the basis of table 1:
Transaction date | ProductID | Product | Pricelist | Price |
01.01.2020 | 100 | ABC | X1 | 10 |
05.01.2020 | 101 | CBA | X2 | 100 |
01.03.2020 | 100 | ABC | X1 | 12 |
08.12.2020 | 100 | ABC | X1 | 14 |
10.12.2020 | 101 | CBA | X2 | 110 |
I found several explanations that are either based on solutions for conditional joining or on joining for a specific date within a time range, but nothing that puts them together in one/ or more expressions. I would be more than happy if you could help me on this!
Thanks in advance and best regards
Solved! Go to Solution.
Hi @mxlfis
I think this is what you are looking for. Convert the dates to the alteryx date format, then join on price list and product id. Lastly, filter for prices where transaction date is between the from and until dates.
Perfect, thank you very much!
Saw this solution in another thread as well, however, i had an immense error in thinking. Now it is clear!
Thanks again and best regards