Forum Discussion
Calculated column
Hello All I need your help in creating the calculated column as below. If Table 1 Product Code = Table 2 Product code and Table 1 product type 16 = “1” in (table 3 city type) then Match...
Hi ryan_mayu Please see below.
If Table 1 Product Code = Table 2 Product code
and
Table 1 product type 16 = “1” in (table 3 city type)
then
Match
Table 2 city code with Table 3 city code and return table 3 country
Else return the country from “0” in (table 3 city type)
For any errors or mismatches return the value as “Mismatch”
DATA
| Table 1 | |
| Product Code | Product Type |
| 0 | 10 |
| 1 | 11 |
| 2 | 12 |
| 3 | 16 |
| Table 2 | |
| Product Code | City Code |
| 0 | AAC |
| 1 | AAE |
| 2 | AAE |
| 3 | GLE |
| 5 | AMR |
| 0 | 28JU |
| 0 | JKLW |
| 0 | "2JK |
| Table 3 | ||
| City Type | City | Country |
| 0 | AAC | AFRICA |
| 0 | AAE | AFRICA |
| 1 | AAE | MALDIVES |
| 1 | AAI | MALDIVES |
| 0 | GLE | INDONESIA |
| 1 | GLE | BHUTAN |
RESULT
| Table 2 | ||
| Product Code | City Code | Country |
| 0 | AAC | AFRICA |
| 1 | AAE | AFRICA |
| 2 | AAE | AFRICA |
| 3 | GLE | BHUTAN |
| 5 | AMR | MISMATCH |
| 0 | 28JU | MISMATCH |
| 0 | JKLW | MISMATCH |
| 0 | "2JK | MISMATCH |
still not clear about the calculation logic.
do you mean check the product code in table 1 based on the product code in table 2?
the line 4, we can see 3 in table 1 , then we found the corresponding product type which is 16. 16 =1, then go to table 3 check city type is 1.
Then what about 10 11 12?