Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Conditional Join incl. Dates

mxlfis
5 - Atom

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 dateProductIDProductPricelist
01.01.2020100ABCX1
05.01.2020101CBAX2
01.03.2020100ABCX1
08.12.2020100ABCX1
10.12.2020101CBAX2

 

Table two:

PricelistProductIDProductPriceValid fromValid until
X1100ABC1001.01.202020.02.2020
X1100ABC1221.02.202001.12.2020
X1100ABC1402.12.202001.01.9999
X2101CBA10001.01.202001.06.2020
X2101CBA11002.06.202001.01.9999

 

Result on the basis of table 1:

Transaction dateProductIDProductPricelistPrice
01.01.2020100ABCX110
05.01.2020101CBAX2100
01.03.2020100ABCX112
08.12.2020100ABCX114
10.12.2020101CBAX2110

 

 

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

2 REPLIES 2
Greg_Murray
12 - Quasar

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.

Greg_Murray_0-1580743320084.png

mxlfis
5 - Atom

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

Labels