Calculated column

[gauravnarchal]

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

[v-xulin-mstf]

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!

[ryan_mayu]

@gauravnarchal 

not clear about this. Could you please separate the table in different rows? It's hard to understand how many columns in each table

[gauravnarchal]

@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

[ryan_mayu]

@gauravnarchal 

which column is the expected result?

[gauravnarchal]

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

[ryan_mayu]

@gauravnarchal 

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?

[Ashish_Mathur]

Hi,

In which Table do you want to see that calculated column?  In that table, show the expected result.

[viviank]

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.

[gauravnarchal]

@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.