Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!
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!