Alteryx Designer Discussions

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

join 2 datasets based on date

Svetlana
5 - Atom

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:

 

DateEC
201808900
201807522
201805658
201712699
201612789

 

2d dataset contains limit data and a start date:

LimitStart Date
1000201301
1100201801
1200201806
1300201808

 

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:

 

DateECLimitMy comment
2018089001300 
2018075221200 
2018056581100 
2017126991000 
2016127891000 

 

 How can I do it using alteryx?

 

Thank you,

Svetlana

3 REPLIES 3
danrh
13 - Pulsar

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:

image.png

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
17 - Castor
17 - Castor

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
5 - Atom

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

Labels