If field is blank, then do a lookup otherwise keep value

Hi everyone,

I'm creating a workflow where I am trying to populate a column. If the column already contains a value, I would like Alteryx to leave that value as is, but if the field is blank, I would like Alteryx to VLOOKUP against another file and pull in the value from that file. I'm using the Find Replace tool, which I think I setup right as I'm selecting the field from the other file that I want to pull from however, I can't figure out how to put a condition to only do that if the field in the base file is empty. Does anyone have any suggestions?

Thanks!

Datasets

Accepted answers

BS_THE_ANALYST

@RCern honestly, the union tool after the join is recent news to me aswell! 😂.

The reason why your record set is growing like this (I suspect) is because CustomerID is not unique? We would expect if the original dataset has 30,778 that we should have 30,778 afterwards. So it looks like CustomerID isn't unique in one of the tables, causing a Many-to-Many match or One-to-Many match (we want One-to-One).

Basically, imagine we are looking up CustomerID in the lookup table. If there are multiple rows with CustomerID 1 on it, the record with CustomerID 1 in our data table will get matched to each of those. Thus making multiple rows for CustomerID 1 (this will happen for lots of customers).

You can check if CustomerID is unique by:
Drag the Unique Tool onto the canvas, select the column you want to check for duplicates:

(P.S) Pay attention to my CustomerID column, you can see there are duplicates in there and there are 11 total records.

Click on the D anchor (duplicates). It will show you if there are duplicates in that column.

All comments

BS_THE_ANALYST

I've tried to replicate your situation:

Data Table:

Lookup table:

Logic to replace the missing values using the supplied lookup table:

If you just want to replace the missing values with a specific value, there are easier ways to do this using an IF statement.
I.e.
IF isnull([Value]) THEN "blah blah"
ELSE [Value]
ENDIF

lookuptable_replacemissingvalues.yxmd

RCern

I should of clarified that my main dataset has 65 columns. One of those I am trying to populate empty cells with a value from another dataset. That other dataset has 25 columns, so I need to match on customer # in both to pull in the correct value if the cell in the original dataset is blank. Sorry if I didn't make that clear .

BS_THE_ANALYST

@RCern no worries. Check this:

Data Table:

Lookup Table:

Joining on CustomerID to bring across that column you want to use to replace the nulls!

2 things:
1) I'm doing a left outer join. If a customer number in the Data doesn't match something in the lookup table, we still want to keep it.
2) From the Lookup Table I have deselected everything as we only want to bring over the Replacement Value from that table i.e doing what a VLOOKUP is doing.

Formula to replace the nulls from the lookup (screenshots below are before and after formula is applied);
Before:

After:

lookuptable_replacemissingvalues_new.yxmd

RCern

After I do the join, then connect the Left Join and The Middle Join to the Union (Which I didn't know you could do by the way lol!) My record set more than doubles. My Original Dataset has 30,778 records. The dataset with the lookup value has 370K. After I do all the joins, I am left with 115K (Somewhere around there). I made sure all the selections mirrored yours. Any suggestions? Thanks so much by the way for taking the time to help!!!

BS_THE_ANALYST

@RCern if the lookup table does indeed have multiple entries for some Customer numbers - we are going to need to determine the logic to pick which one of those you want to attach the Customer# in your main data table.

RCern

I got it to work! Thank you so so much!!!! Huge help!

BS_THE_ANALYST

@RCern Great stuff!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703