Forum Discussion
IF formula with column from another table
Hi,
I am trying to build formula that will calculate last column in example shown in desired result table. The problem is that value column comes through 1 to many relationship from other table.
I have tried related and relatedtable, without any luck.
Desired table outcome
| ID | code | value | Desired outcome for value |
| a1 | 1111 | 13 | 13 |
| a2 | 2222 | 43 | 43 |
| a3 | # | 22 | NA |
| a4 | 4444 | 82 | 82 |
Other table connected with one to many relationship.
| ID | value |
| a1 | 13 |
| a2 | 43 |
| a3 | 22 |
| a4 | 82 |
So, basically i just need to change content of value column according to code column. Normally I would use IF( ) but I dont know how to access this other column from another table.
I would apreciate any suggestions 🙂
Thank you.
you can get a new column like this
New column in Table 1 = if(table1[code]="#","NA",maxx(filter(table2,table1[ID] = table2[ID]) ,table2[value]))
4 Replies
- amitchandak
Super User
Not very clear. But this how you copy columns from one table to another
New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value]) New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])Hey, thank you for replying.
I have tried your formula, but it doesn't really work and I don't really understand it. When I am trying to write this formula in the original table, table1[customer] does not find anything.
What I am trying to achieve is to get access based on vallue in table 1 to value from column in table2. I already have the relationship established.
I would like something like:
IF( value in code == "#", NA, get value from columns in table2) based on the established relationship.
- amitchandak
you can get a new column like this
New column in Table 1 = if(table1[code]="#","NA",maxx(filter(table2,table1[ID] = table2[ID]) ,table2[value]))