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

    Combine 2 phone lists into 1

    Regina Ku

      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 CustomerPhonePhone Mobile
      291066488310
      29109430074294300742
      2910 94300742
      2910 91158893
      29106648831197474087
      2910 97243663
      66281 86111669
      66281 84983439
      66281 97205780
      662819177030491770304
      66281 86129940
      662816510790096920356
      66281 96920356
      66281 82862803
      6628181185133
      33425 96921111

       

        

      Combined phone list
      Id CustomerPhone Combine
      291066488310
      291094300742
      291066488311
      291091158893
      291097474087
      291097243663
      6628191770304
      6628165107900
      6628181185133
      6628186111669
      6628184983439
      6628197205780
      6628186129940
      6628196920356
      6628182862803
      3342596921111

       

      Output. 

      Id CustomerNo. of distinct phone numbers
      29106
      662809
      334251

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

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

       

      Appreciate any guidance! thanks!

      Regina