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
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”
| Table 1 | Table 2 | Table 3 | ||||||
| Product Code | Product Type | Product Code | City Code | City Type | City | Country | ||
| 0 | 10 | 0 | AAC | 0 | AAC | AFRICA | ||
| 1 | 11 | 1 | AAE | 0 | AAE | AFRICA | ||
| 2 | 12 | 2 | AAE | 1 | AAE | MALDIVES | ||
| 3 | 16 | 3 | GLE | 1 | AAI | MALDIVES |
9 Replies
I know this isn't directly answering your question, but I recommend setting up the relationship in the data model itself. Link the Product Codes together in the data model, and link the City Codes together in the model.
If you do that, then you don't need a formula, you can simply grab the Country code and it will work automatically.
If you need to filter by Product Type 16, you can add that as a filter to the visual.
viviank - I need to create a calculated column for getting the desired result. The visual filter will not work.
Also, in table 3 there are cities which had got different countries.
not clear about this. Could you please separate the table in different rows? It's hard to understand how many columns in each table
ryan_mayu - Here is how the data look like in the separate tables.
Thank you
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 Table 3
City Type City Country 0 AAC AFRICA 0 AAE AFRICA 1 AAE MALDIVES 1 AAI MALDIVES Hi,
In which Table do you want to see that calculated column? In that table, show the expected result.
Hi gauravnarchal ,
Is your issue solved?
I'm not sure about the logic of 'Table 1 product type 16 = “1” in (table 3 city type)'.
Could you provide more details?
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
