Forum Discussion
Helper INeed help with relationship between two tables
Hi All,
I am having two tables between which there is no relation plus there is no unique key to identify unique records.
COls from table A to be used
| Year-Quarter | Cost Centre | OP_expense_Sub_cat | Amt_InQ_000s_Actual1 | Actual Allocation1 | Amt_InQ_000s_Budget1 | Budget Allocation1 |
COls from table B
| FC_LOB | Cost Centre | Function code | Act SKF% |
Final Expected result from both tables
| Year-Quarter | FC_LOB | Cost Centre | Function code | OP_expense_cat | OP_expense_Sub_cat | Amt_InQ_000s_Actual1 | Act SKF% | Actual Allocation1 | Amt_InQ_000s_Budget1 | Budget Allocation1 |
The cost centre in both the tables are not unique and for one costcentre there are multiple function codes. i want this func code col and act skf% to be in the table A
Both table have many to many relationship created and i have even added the cols from both the table in table visual. i am using related func and lokup func to add the table b col in table A but i get errorrs for multiple values are there where single value is expected.
How can i achieve this..
Appretiate quick help
Hi Everyone,
Thanks so much for your replies.
I found the solution to my problem and here is what i did.
I created a bridge table between these two tables and added al lth eunique cost centres there and then indivisually joined tables by oneto many relationship as
Table A many to one relation with Bridge table and Table B many to one relation with Table A and make it active and also filter direction to be selected as both.
Then createe 2 measures in the table 2 where i wanted to have the logic updated
Following all these steps my issue gor resolved.
13 Replies
- d_m_LNK
Super User
How are you trying to add the column? Within a measure on the visual or with a calcualted column on the table? The error you are getting is usually a context error where the function you wrote is expecting a single value (row context) but a column of multiple values is referenced instead of a single value.
Could you paste the code of your measure/calculated column for us to better understand what's going on?
UHSHello,
PFB data for referenceTable A Table B Table B Table B Table A Table A Table A Table B Year-Quarter LB CC FC Cat Subcat Amount Act% 2024-Q1 NonCore 100000 0000-AA000 Compensation Bonus 7.284 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Other Benefits 13.7054 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Pension Expense 5.70776 99.6 2024-Q1
NonCore 100000 1111-AA1111 Invest Exp Invest Exp -31.39126 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Bonus 7.284 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Other Benefits 13.7054 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Pension Expense 5.70776 0.4 I have highlighted the columns to identify which is coming from table A and B. Many to many relation is created between these two tables and this is what i see in my report visual right now.
Now i want to create two logics, i.e. if the cat is Invest Exp then Act% should be 100 else as it is and Fc should be AA1010 else as it is. so expected result should be as below :How can i achieve it.
Note: @ different tables.. Joined based on mamuy to many relationTable A Table B Table B Table B Table A Table A Table A Table B Year-Quarter LB CC FC Cat Subcat Amount Act% 2024-Q1 NonCore 100000 0000-AA000 Compensation Bonus 7.284 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Other Benefits 13.7054 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Pension Expense 5.70776 99.6 2024-Q1 NonCore 100000 1111-AA1010 Invest Exp Invest Exp -31.39126 100 2024-Q1 NonCore 100000 1111-AA1111 Compensation Bonus 7.284 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Other Benefits 13.7054 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Pension Expense 5.70776 0.4
- d_m_LNK
Thanks for the sample data, with which columns are you relating the tables?
- UHS
Hello Ryan,
thanks for your message.
PFB data for referenceTable A Table B Table B Table B Table A Table A Table A Table B Year-Quarter LB CC FC Cat Subcat Amount Act% 2024-Q1 NonCore 100000 0000-AA000 Compensation Bonus 7.284 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Other Benefits 13.7054 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Pension Expense 5.70776 99.6 2024-Q1
NonCore 100000 1111-AA1111 Invest Exp Invest Exp -31.39126 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Bonus 7.284 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Other Benefits 13.7054 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Pension Expense 5.70776 0.4 I have highlighted the columns to identify which is coming from table A and B. Many to many relation is created between these two tables and this is what i see in my report visual right now.
Now i want to create two logics, i.e. if the cat is Invest Exp then Act% should be 100 else as it is and Fc should be AA1010 else as it is. so expected result should be as below :How can i achieve it.
Note: @ different tables.. Joined based on mamuy to many relationTable A Table B Table B Table B Table A Table A Table A Table B Year-Quarter LB CC FC Cat Subcat Amount Act% 2024-Q1 NonCore 100000 0000-AA000 Compensation Bonus 7.284 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Other Benefits 13.7054 99.6 2024-Q1 NonCore 100000 0000-AA000 Compensation Pension Expense 5.70776 99.6 2024-Q1 NonCore 100000 1111-AA1010 Invest Exp Invest Exp -31.39126 100 2024-Q1 NonCore 100000 1111-AA1111 Compensation Bonus 7.284 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Other Benefits 13.7054 0.4 2024-Q1 NonCore 100000 1111-AA1111 Compensation Pension Expense 5.70776 0.4
ryan_mayuPls provide the sample data for table A and B separately.
- UHS
Thanks for yuor reply, my issue got resolved
- UHS
Hi Everyone,
Thanks so much for your replies.
I found the solution to my problem and here is what i did.
I created a bridge table between these two tables and added al lth eunique cost centres there and then indivisually joined tables by oneto many relationship as
Table A many to one relation with Bridge table and Table B many to one relation with Table A and make it active and also filter direction to be selected as both.
Then createe 2 measures in the table 2 where i wanted to have the logic updated
Following all these steps my issue gor resolved.
v-dineshyaCommunity Support
Hi UHS ,
Thanks for the update and happy to know you have found a solution. Mark it 'Accept as Solution'. It may help others who may be in similar scenarios.
Thank you.
