Join function only joining one value vs all values

[Bkylefoster]

I'm attempting to join two data sources but the workflow is only pulling one of four values in.  The Delivery Selection has four options (A,B, C, D) however once I join only option B is pulling in.  No other options (A, C, D). 

To add, each option has four delivery categories, and of the B option only the first is pull through.

Ideally there would be four options for each Load ID and all Delivery Selections.

[Bren_Spill]

@Bkylefoster - can you provide some more detail? If you can't provide actual data or workflow, then maybe screen shots of the L and R outputs from the Join tool

[danilang]

Hi @Bkylefoster 

The screen shot you shared is only showing the results window of the join tool.  It's only showing the first 16000 of 380000 records of the entire results set.  Alteryx does this to reduce memory use in the results windows.  The join tool also implicitly sorts your records so all the B records end up together.  Add a Browse tool after your J output and you should see all the joined records.

Dan 

[Bkylefoster]

 You can see the two data sources share the delivery selection but when I look at the join they only pull in B. 

[Bren_Spill]

@Bkylefoster - If you could send screen shots of the L & R Join outputs that would be helpful. I'm assuming in the L anchor you have 12 records (A, C, D records)?

Also, you're creating a ton of duplicates; is that the intention?

[danilang]

hi @Bkylefoster 

Here is a detail of your original screen shot.  

Notice the green box.  This is showing you that Designer is only displaying the first 16,000 records of 380K.  This is to reduce memory consumption when running workflows.  Here's an excerpt from the Alteryx Help page

 

To see your entire data set, add a Browse Tool after your join and look at the results there.  Designer displays the entire data set in the Results grid of a Browse tool.  You'll have to scroll down quite far since the Join tool also sorts your data using the joined fields.  

Another way to show that you have all the abcd values is to add a summarize tool after the J and group by DeliverySection and Count one of the other fields.  This will show you the total number of records for each DeliverySection 

Dan