11 Replies Latest reply on Jun 11, 2018 4:54 AM by Sohan Jawar

# Problem in Consolidating two fields to create unique field

Hi All,

I have two country fields like country A and Country B.  I wanted to add the values from two fields to create consolidated field.

For Ex:

Below there are two fields `Country A`  And `Country B`. I need  to Create result set like` Country C`[ where Singapore =15(10+5 )and India=10(6+4)]

 Country A Singapore 10 Austarlia 4 Japan 12 India 6

 Country B India 4 Honkong 7 Singapore 5 Malaysia 14

 Country C Austarlia 4 Honkong 7 India 10 Japan 12 Malaysia 14 Singapore 15

• ###### 1. Re: Problem in Consolidating two fields to create unique field

How is the data stored ?

Do you have 3 datasources ?

• ###### 2. Re: Problem in Consolidating two fields to create unique field

Hi ,

Only one datasource.

Regards,

shwetha

• ###### 3. Re: Problem in Consolidating two fields to create unique field

Hhhmmm .... I still don't understand the way you're storing the data.

Can you provide a better example, like a spreadsheet ?

• ###### 4. Re: Problem in Consolidating two fields to create unique field

Hi,

Below is the screen shot showing one datasource and fields(country A  and Country B)

Regards,

shwetha

• ###### 5. Re: Problem in Consolidating two fields to create unique field

Hi Shwetha,

If your data looks like this

Country A     Country B      Measure 1    Measure 2

Singapore     Singapore     10                 5

India              India              7                  8

Then you need to do the following calc:

Sum(Measure 1) + Sum (Measure 2)

If you only have one measure column then you need to make sure that the country names match exactly across Country A and Country B.

Joe

• ###### 6. Re: Problem in Consolidating two fields to create unique field

Can you put both Country A and Country B in rows and send a screen shot please.

Joe

• ###### 7. Re: Problem in Consolidating two fields to create unique field

I think you need to revisit the data before you start using Tableau.

How was the data created ?

What is the difference between Country A and Country B ?

Also there's issues within both dimensions - AUSTRALIA and Australia are different values and the same with Bangladesh.

Basically, you need to fix the data and sort out the problems there before you start using Tableau.  Then the data will be easier to use and it will be easier to do what you want.

• ###### 8. Re: Problem in Consolidating two fields to create unique field

Hi Joe,

Thank you so much for reply.

Below is the screen shot where Country A and country B in rows.

Regards,

shwetha

• ###### 9. Re: Problem in Consolidating two fields to create unique field

Hi Joe,

That can be fixed by using `upper `method of tableau to convert uppercase like below.

This data is about network services. some times circuit will be installed between two countries(A&B). sometimes it is installed in different location within same country, in that case counry B will be left blank.

here, we need to add like below

ex: 1)total number of circuit ID where country A='Austaralia' +total number of circuit ID where country B='Austaralia'

2)total number of circuit ID where country A='India' +total number of circuit ID where country B='India'

That will give total number of circuit installed in particular country.

• ###### 10. Re: Problem in Consolidating two fields to create unique field

Hi Shwetha,

I think you need to go back and look at your data as Chris has already mentioned. In the example you show, Algeria is matching up with South Korea which is not correct.

Once this is sorted i would give Tableau another go.

Joe

• ###### 11. Re: Problem in Consolidating two fields to create unique field

Hi,

In Such a case try creating a custom SQL as below.

SELECT [Country A],SUM([Measure]) FROM [Table] GROUPBY [Country A]

UNION ALL

SELECT [Country B],SUM([Measure]) FROM [Table] GROUPBY [Country B]

If you use this as data source then the Country A and Country B both will become one dimension and then you can SUM the Measure accordingly for the same country.

Regards

Sohan.