Join in ETL

caiorogano

Question:
I have two separate databases where there is only one common column, which is the "salesperson."

• The first database contains sales values for each salesperson.
• The second database contains the area of operation and products associated with each salesperson.

I plan to create a new column to calculate the value/area of operation ratio. The challenge is that when I filter out products, I want the sales value to remain constant, but the area of operation should decrease, thus increasing the value/area ratio.

How can I merge these two databases, ensuring that I can calculate the adjusted value/area ratio dynamically as I filter products, while keeping the value intact?

Any advice or step-by-step guidance would be greatly appreciated!

This version adds the specific calculation and filtering process you're aiming for.

Data_Devon

Hello caiorogano!

Why would the sales remain constant even if products are removed?

Also - what is your relationship between the datasets assuming that Salesperson is your Join Key? Is there one row per Salesperson, and values/area of operation/products are pivoted out to new rows?

I want to make sure I understand before I offer my two pennies. Perhaps a GOAT can read through the lines to help, but I don't want to lead you astray with insufficient information.