IF formula with column from another table

[Anonymous]

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.

[amitchandak · Accepted solution]

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]))

[amitchandak]

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])

[Anonymous]

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 · Accepted solution]

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]))

[Anonymous · Accepted solution]

@amitchandak 

Work like a charm, thank you a lot!

I was even able to make it more complex.