Need help - Joining two tables based on multiple join conditions (date range & ID)

Hi Community,

I am stuck with a problem and was hoping if someone could help me solve it. I have fact & product table and need to join those two based on product id and a particular date range where the transaction row of fact table can determine the minimum price for a product.

As an example, I would like to see the attached screenshot as an output. Attached are all the relevant files.

Appreciate your help in advance!

Thanks,

Join based on Date Range.yxmd

Facts.xlsx

Join based on Date Range.yxzp

Output example.JPG

Products.xlsx

Date Time

Join

Expression

Accepted answers

apathetichell

Hi - This is a simple update...

Remove the generate rows from the upper part of your join.

Change the join tool - Instead of joining on RowCount/Date from your minimum table vs - the RowCount/Date from your upper/order/product table - join on RowCount/Date from your minimum table vs the start date.

This should get you what you want. Play around with it.

ArtApa

Hi @udles5666 - I added a logic to deal with L-Output. Hope this is something that you wanted to achieve.

Join based on Date RangeAA.yxzp

All comments

apathetichell

Not sure I 100% follow this. Look at line 12- you have presumed transaction with a start/end date which cover two minimum price ranges. Which one do you want? both?

RecordID Customer Product Code Product Description Promotion ID Start Date End Date Status Amount ROI minimum price
12 Customer B 5080 Product B 339848 2022-01-10 2022-10-31 Completed 1.8846 -1 4.67
12 Customer B 5080 Product B 339848 2022-01-10 2022-10-31 Completed 1.8846 -1 4.38

Basically your strategy is going to end up being to generate rows for the date range for your minimum dates (or your order dates - or both). For your order dates - you'll need to clean your data to turn the values into dates. You can use the date/time tool - or a formula tool - a select tool will drop some values. then you basically join on product/date form your generate rows and then use a summarize to extract only the data you want (in groups.). Potentially your workflow will end up looking like this:

Screenshot (479).png

Try building it and see where you get.

udles5666

Hi @apathetichell

Thanks for looking into this.

I only expect one entry which is 4.67 one bucket. Can I please request you to attach the exported workflow so that I can replicate & check how this is done. Thanks heaps,

apathetichell

Here's the .yxzp - which should basically get you there. Still not clear on the logic.

Join based on Date Range.yxzp

udles5666

Hi @apathetichell

I see what you mean now. Apologies, that's not what I was after. I have tidied it up the dummy data to make it less confusing.

The focus table should be fact table. The product id needs to be looked up in the Product Table and match the date range of the minimum price. Only one record should be there for one promotion id

I have added an extra column in the fact table to derive the expected result & described the logic. Can you please have a look again to see if you can solve this for me?

Attached updated flow.

Thanks,

Join based on Date Range_v01.yxzp

Only 1 record should be the result for one promotion.JPG

udles5666

Hi @apathetichell

Legend - thank you. This is what I was after, it worked.

Only one complication now.

When there is a transaction in the Fact (main/primary) table where there are no hits on the join I want the minimum price to be based on the max end date of the product (minimum) table. How can I edit the generate rows expression so that it takes into account that if anything is more than the end date it gives me the output from the very last price we had for that product code. I need to ensure there are 0 returns on the left join.

Attached updated workflow with desired output screenshot.

Your help and guidance is very much appreciated.

Thanks,

Join based on Date Range_v02.yxzp

ArtApa

Hi @udles5666 - I added a logic to deal with L-Output. Hope this is something that you wanted to achieve.

Join based on Date RangeAA.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703