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

[udles5666]

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, 

[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:

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. 

[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, 

[apathetichell · Accepted solution]

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.

[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, 

[ArtApa · Accepted solution]

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