What does this message about my Join tool mean?

[TH]

Working on challenge 481.

Built an iterative macro as part of my solution. The idea is that it will pull in the data, split part of it off, do a join for some processing, and send the rest back around to go through the macro again. That part, at least, appears to be working properly, and it is not the issue that I am seeing.

What I seem to be unable to figure out is why the join tool inside the macro is doing what it is doing.

In the first iteration the L input anchor gets 45525 records. The R input anchor (which includes the records going into the L input anchor) has 218953 records.

I turned on macro messages to try to debug the macro and found that with these inputs I get this line:

" 481 iterative macro (74) Iteration #1: Tool #12: 707119544 records were joined with 0 un-joined left records and 30244 un-joined right records"

I am at a loss for what that means.

Does it think that 707 million records are getting input?

[alexnajm · Accepted solution]

It means 707 millions records are getting output from your J anchor - likely there is duplication in the keys!

[apathetichell]

yup --- some basic explanation here: when you use a join it works like this:

data source a: [1,2,3,4,5]

data source b: [1,1,2,2,3,4,5,5]

when you join ---> you do not have a discrete list of values --- so your resulting data set is:

[{1,1},{1,1},{2,2},{2,2},{3,3},{4,4},{5,5},{5,5}] ---> this can explode fast..

the fix is usually to add a secondary join characteristic ---ie another column, and or to use the summarize tool (or unique tool if you must) on one of your datasets.I hate the unique tool. but that's just my opinionated take.

I think this is going to be a hard challenge for you ---> but I'd recommend sticking with it. building iterative macros can be challenging so after you you get over this hurdle, don't be discouraged if you hit more... One hint on iterative macros is that you need to avoid field sprawl --- this is

when your macro adds a new field --- and you don't remove it before the loop --- and it means that when you run macro time 1 you have fields:

{field1, field2... fieldN} but on successive runs you have {field1, field2,...fieldN+number of times through the loop+1} --- use a select tool to control schema.