Creating a join expression with multiple criteria

s_f_OTT

Hi,

I am a little stuck trying to create a join expression. In the table below, I want to join the Cost using the active From date to a table of data with multiple dates. For example, I have data from 1/31/24 thru today. For Campaign 1, I want to use the cost of .5 up to 1/31/2025 and then moving forward after that, use .6 as the cost.

I currently have the expression Raw Data.Date >= Table Below.Active From which will only work on one of the 2 dates. I have thought about using Upper and Lower, but then I think about, "What if I have a 3rd option for Campaign 1.

Any thoughts?

	Active From	Cost
Campaign 1	1/31/2024	0.5
Campaign 1	2/1/2025	0.6
Campaign 2	1/31/2024	0.5

rco

This would be three tiles, a Join, a Rank & Window, and a Filter. Start by joining like you have, where every date joins to every campaign with an active date less than or equal to it. Then you just need to reduce that down to just the row with the greatest active date. In the Rank & Window, partition by a row identifier of some sort from the date table[1]. Sort by Active From descending. Then add the window function ROW_NUBMER, naming it rank. Then, in your filter tile, filter to rank = 1.

1. Raw Data.id or something like that. If you don't have a row identifier of any kind in Raw Data then the first thing you'd need to do is add one. Use Add Formula with the ROW_NUMBER() function.

s_f_OTT

I still want to have everything before 2/1 to be .5 and everything after to be .6. Then what if a 3rd date gets added?

rco

I assume you mean you want everything before 1/31/2024 to be .5 and everything after 2/1/2025 to be .6. In other words, you want the earliest campaign to apply on into the past forever, and for the latest campaign to apply on forever into the future, or at least until another campaign is added with a later date?

The solution I've described already fulfills that second condition. Dates greater than the latest campaign will join to only it, and that campaign's cost will be the one selected by the Rank & Window → Filter. If another date is added that's later, but less than the date in a row of Raw Data, then it will join to that as well and that latest Active Date will be the one to survive the Rank & Window → Filter instead.

To fulfill the first condition, I would just make it a left outer join, so that you get every row of Raw Data, even when the Date is before any campaign's Active From . Those unjoined rows will have null for Active From and Cost, so I would add a formula after the join to rewrite the Cost column, replacing those nulls in Cost with the desired before-all-campaigns value:

Add Formula

Cost: IFNULL(Cost, 0.5)

rco

It may be easier if I explain it like this:

First off, I'm assuming that your Campaign table has just one row for each Campaign, where the Active Date is the date it begins. From your example, it seems like you might have two rows per Campaign, though, and I'm not sure how to interpret those. The first thing we need to do is change that Campaign table so that it only has one row per Campaign, so we know what the Cost should be for each one. I'm not sure what we would do if we had multiple rows for a single Campaign with different costs. What does that mean?

If your Campaign table currently consists of one or two rows per Campaign, and the second row, if present, represents the end of the Campaign, and we can safely ignore the Cost from the end-of-campaign row, then you can just use a Remove Duplicates tile using the campaign name or ID as the uniqueness column, and it will reduce the table down to the form we need, with just a row for the start date of the campaign.

1. We use Join to connect every Raw Data row to every Campaign row where Campaign.Active Date is before Raw Data.Date. The result of this join will contain a mix of rows you want and rows you don't want.

2. We use Rank & Window to rank the Campaign rows that each Raw Data row joined against. What we're trying to do is assign rank #1 to just one of the joined rows for each Raw Data row. The sort order will be Active Date descending, so that rank #1 will be the most recent Campaign that the Raw Data joined against.

3. We use filter to select just cases where rank = 1, bringing us back to just one row per Raw Data row, with only the latest active Campaign with an Active Date before our Date joined to it.

s_f_OTT

I do not really understand what is happening but it seems to be working. The one thing I do not want to do is filter out previous dates that had an older Cost.

s_f_OTT

Ok, if I take out the filter, it does not work how I would like it to. It is taking the old Cost and the new Cost and duplicating the rows to assign both of them.

rco

If you have the Join → Rank & Window → Filter, and:

1. You're using left outer join with Raw Data on the left
2. You have your Rank & Window partition by a unique ID column of Raw Data
3. You're using the Row Number function in Rank & Window
4. Your Filter condition is rank is equal to 1 (where rank is the column containing the Row Number in Rank & Window)

…then you should have exactly one result row for every Raw Data row. The Campaign info attached to the row will be determined by the join condition (which should be Raw Data.Date >= Campaign.Active From) and your Rank & Window's sort order (which should be Active From descending).

However, you may still have rows that have no Campaign info on them. These are rows whose Dates were not greater than any of the Campaign dates. I don't know how you want to handle those, but you can do so using an Add Formula at the end, which overwrites the Cost and any other of the Campaign columns that you may want.

If you want to attach the earliest Campaign to all the rows that are before any campaign, you can do that as well, but it's a little more complicated and will involve another join.