Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Joining data and assuming nulls

Tbrommen
5 - Atom

Hi Everyone,

 

So I have 2 data sets that look like this:

CityEQPTMonthYearCompletion Factor
CIN7876201784.82%
FIN7376201796.68%
BIN74762017116.05%
HOM7176201773.38%
AUS73762017113.97%
ATL7876201798.14%
ATL78762017100.41%
CityEQPTMonthYearTripsMinutesHoursMiles
CIN78762018753823151371.918451605
FIN7376201954657.752513
BIN7476201715614875247.91780558
HOM7176201813315475257.91792117
AUS7376201913714950249.16784109
ATL78762019704789401315.666433632
ATL78762017667710351183.917399953


What I am attempting to do is join these data sets to add the completion factor column for all rows that match by City, EQPT, Month, and Year.  Then for all of the rows which receive a Null for completion factor (because it did not exist from data set 1), I want to use the previous year (same month and EQPT) completion factor.  Additionally, if the EQPT for a previous year (same month) does not exist, how could I stipulate to use the completion factor for a different EQPT, but in the same Month/Year?

 

I hope this explained it well enough.

2 REPLIES 2
CharlieS
17 - Castor
17 - Castor

You will need a few Join tools to achieve this, with each subsequent round using the unjoined records from the previous round. I've created the attached example solution to get you started. Also, I didn't know how you wanted to handle multiple matches so I removed the second ATL factor record to simplify the example. 

Tbrommen
5 - Atom

Thank you Charlie!  I was able to make this work in the way that I needed.  My end result ended up looking like this:

image.png

 

I was really struggling with trying to figure out outer joins, but your example made it click!  Thank you again!

Labels
Top Solution Authors