join 2 datasets based on date

[Svetlana]

Hello,

I am new to Alteryx and there is a problem that I can't solve.

I have 2 datasets:

1st dataset contains economic capital data for month/year:

Date	EC
201808	900
201807	522
201805	658
201712	699
201612	789

2d dataset contains limit data and a start date:

Limit	Start Date
1000	201301
1100	201801
1200	201806
1300	201808

I need to have a join table of Date, EC and Limit which was valid for the given date. For example, the limit 1300 was set on 201808, so for any EC data>=201808, I need to display 1300 limit.

The limit 1200 was set on 201806, so for any EC data between 201806 (included) and 201808, I need to display 1200 limit...

etc:

Date	EC	Limit	My comment
201808	900	1300
201807	522	1200
201805	658	1100
201712	699	1000
201612	789	1000

 How can I do it using alteryx?

Thank you,

Svetlana

[danrh · Accepted solution]

Rather than using a Join, you could use a Union and then a Multi-Row Formula tool to copy down the limits. Something like this:

This way you don't have to deal with in unequal dates. Take a look, see if this works for your situation.

Hope it helps!

[CharlieS · Accepted solution]

I approached this by combining both data sources into one, chronologically sorted, data set. Then I filled the limit field with the last specified limit and filtered the results to the populated EC data.

An example solution is attached.

[Svetlana]

Thank you very much for the hints! It works!!!!