Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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