Comparing one row of a table with every other row / commonality testing

[#1]

Hi!

Example data below. I am attempting to compare holdings of individual stocks in each account with a designated "master" account - highlighted orange in the example data - take the minimum, and then sum up the minimums. Effectively working out the "commonality" between each account and the master account.

	Stock 1	Stock 2	Stock 3	Stock 4	Stock 5	Stock 6
Account 1	20%	15%	17%	14%	19%	15%
Account 2	19%	17%	17%	11%	20%	16%
Account 3	21%	16%	18%	12%	18%	15%
Account 4	18%	14%	19%	12%	19%	18%
Account 5	17%	15%	17%	15%	21%	15%

Pseudotable:

	Stock 1	Stock 2	Stock 3	Stock 4	SUM
Account 1	20%	15%	17%	...
Account 2	MIN(Account 1, Account 2)	MIN(Account 1, Account 2)	MIN(Account 1, Account 2)	...	SUM(Row)
Account 3	MIN(Account 1, Account 3)	MIN(Account 1, Account 3)	MIN(Account 1, Account 3)	...	SUM(Row)
Account 4	...	...	...	...	...

I am able to separate out the account 1 data using an alternate state, however when bringing this data into a table from the default state using set analysis it will not populate in the "Account 2", "Account 3" etc rows as the accounts have been filtered out of the alternate state.

I may well be overthinking this - is there a simpler way to do this?

[#2 · Accepted solution]

I ended up creating another table with "Stock" and "Weight_LEAD" as fields in the load script. That table then joins automatically on stock with the main table.

Much simpler!