2 Replies Latest reply on Oct 21, 2016 2:11 AM by Arijit Ghosh

# Combine 2 phone lists into 1

Hello,

Still new to Tableau, need some help. I have 2 phone lists which reside in the same table - 'phone' and 'mobile phone'. Each phone number is linked to a Customer ID. It is possible for a customer ID to have multiple Phone and Mobile Phone numbers. The data source is from customer form entry per transaction, customer might fill in different phone information each time.

My aim is to determine for each unique Customer ID, how many unique phone numbers each customer has. Thus I'll like to combine the 2 phone lists together, and do a distinct count of unique phone numbers for each Customer ID. So far all the solutions i've seen is to add both columns together [phone]+[phone mobile], which is not what I'm looking for

see example below:

 Original phone list Id Customer Phone Phone Mobile 2910 66488310 2910 94300742 94300742 2910 94300742 2910 91158893 2910 66488311 97474087 2910 97243663 66281 86111669 66281 84983439 66281 97205780 66281 91770304 91770304 66281 86129940 66281 65107900 96920356 66281 96920356 66281 82862803 66281 81185133 33425 96921111

 Combined phone list Id Customer Phone Combine 2910 66488310 2910 94300742 2910 66488311 2910 91158893 2910 97474087 2910 97243663 66281 91770304 66281 65107900 66281 81185133 66281 86111669 66281 84983439 66281 97205780 66281 86129940 66281 96920356 66281 82862803 33425 96921111

Output.

 Id Customer No. of distinct phone numbers 2910 6 66280 9 33425 1

I believe this is the correct calculated field formula to use?

{ FIXED [Id Customer]:COUNTD([Phone Combine])}

Appreciate any guidance! thanks!

Regina

• ###### 1. Re: Combine 2 phone lists into 1

Hi Regina,

You can try using Pivot to make the two phone numbers to one and then use CountD on that.

PFB Steps and use the attached sheet too.

Right click the two columns - Phone, Phone Mobile

Hide the Pivot Field Names & Rename the Pivot Field Values to Phone Number.

Make the phone number to String.

Now Try COuntD

• ###### 2. Re: Combine 2 phone lists into 1

Hi Regina,

Another approach could be use coalesce or nvl function in custom sql. coalesce(phone,mobile) in first query and in second query coalesce(mobile,phone) then union this 2 query. You would get the desired result.

(Example I used is of Oracle database, feel free to reply for further clarification)

Regards