Why is performing a JOIN increasing my total DB value?!

(I'm going to find it hard to upload sample data - it's proprietary and large - but will try if no-one can make sense of the below.)

+ I have data - 779k records. Total value: £13m.

+ I summarise - 55k records. Total value: £13m.

+ I run a JOIN. Input:

LEFT: My 55k records

RIGHT: A reference sheet, in which I'm pulling in only BARCODE and PRODUCT TITLE, joining on BARCODE and pulling in the PRODUCT TITLE on a match.

+ The join output:

RIGHT: Irrelevant

LEFT: 290 records, SUMMARISE value £8k

JOIN: 68,618 records, SUMMARISE value £15.5m(!!)

I simply do not understand how this happened. All the JOIN should be doing is pulling in a text value if it matches barcodes. How is it increasing the number of records and the value in the db?

The Join is reading off an Excel - my current theory is that perhaps that reference excel lists the some barcodes twice but with a different text entry for each listing. Would the JOIN then duplicate the record it's matching to in the non-reference document, so it can put a match one with each of the found text entries? (I have checked and this is not the case)

Join

Accepted answers

Aguisande

Hi @ChillEDog,
For what I understand of your example, the structure of your data is something like this:

What I would do is to Summarize the INVENTORY table, grouping by [barcode] and SUM([units]). That'll give you a unique value for [barcode] with the total of values summed.

It's result is:

Then you can join against your other table.
If I misinterpreted anything of your use case, please let me know, and we'll see it.

All comments

Aguisande

Hi @pupmup

What's happening is that you have more than one matching records per concept in one (or both) datasources.

Remember that Alteryx returns Joined records through the J output, and Unjoined ones through the L & R outputs.

Maybe if you summarize(Group by) or Unique the BARCODE for the R input, and then try the join, maybe you'll get close to what you need.

Regards

ChillEDog

simple example but hope it helps

suppose you have one row on the left value 100 and it has barcode "abc"

on the right you have 10 rows, 3 of which have barcode "abc"

the join on barcode "abc" will produce 3 rows, each having value 100

is there additional field(s) that can be added to the join to make it unique ?

Aguisande

Hi @ChillEDog,
For what I understand of your example, the structure of your data is something like this:

What I would do is to Summarize the INVENTORY table, grouping by [barcode] and SUM([units]). That'll give you a unique value for [barcode] with the total of values summed.

It's result is:

Then you can join against your other table.
If I misinterpreted anything of your use case, please let me know, and we'll see it.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703