Find and Replace is more performant than Join Assertion

AnnArborThom

I attended an Alteryx Tips and Tricks session and this years Inspire conference and the "ACE" asserted that:

• In some situations a find and replace can be used instead of a join
• And when this is done it is faster by a factor of a 100

I am having trouble understanding what cases a find and replace can be used instead of a join. 

The ACE did state this can be done in only certain circumstances. 

Can anyone explain the use case in which this is possible? 

MarqueeCrew

@AnnArborThom,

Suppose you have a large file, maybe 10 Million records, maybe 150 Million records.  The weight of each record is a factor of the number of fields and size of each field.  Now suppose that one of those fields is a coded value with domains of [A-Z].  If you wanted to include the description of each value and you were going to join the data to a metadata table, then Alteryx would sort each of the files into domain orders (A-Z).

That is going to take much more time than it would to do a quick lookup into a table of 26 values and put the right description onto the records (no sort required).

Find Replace allows for roughly 32,000 values to be put into memory for lookup (lookup allows for partial match and case insensitive match too).  You can replace the value or append other values to the existing record.  The use of Find & Replace is going to save you lots of time in this case, not to mention that it will allow all records on output without having to use a union for unmatched records.

Cheers,

Mark