Joins where "left" dataset contains blanks

[DavidOliverChapman]

I have a third-party dataset showing transactions for various geographies across EMEA. I have - seperately - a list of in-house classifications (for instance, grouping different types of retail use. I have tried doing a join to join the in house fields to the third-party data. I have tried to use the "Use" column - retail, logistics, data centre etc. - to append the in house descriptors to the dataset. However, as the in-house descriptors do not cover all of the categories in the use column, it the resulting joined dataset is missing a large number of records.

I am thinking in terms of a VLOOKUP almost, where I could use an if statement to effectively say if the USE column contains Retail, then lookup, else, "".

Is there a better way to do this (inevitably, yes!), or any suggestions about how to ignore, but keep, blank entries?

DOC

[GiuseppeC · Accepted solution]

Hi @DavidOliverChapman

have you tried the Find and Replace tool in the Append mode?

Hope this helps!

Giuseppe

[danilang]

Hi @DavidOliverChapman 

Assuming you have your 3rd party connected to the L and In-house connected to the R

The standard way to handle this is to use a union after the join to combine the records  coming out of the L and J outputs.  Of course, you'll have null in the unioned table corresponding to columns from the R input that did not match, but you'll get all the records from the L input

Is this what you're looking for or are you trying to infer values for the records that don't match?

Dan