9 Replies Latest reply on Sep 8, 2017 1:22 PM by Josh Delekta

    Mapping Multiple Names and Addresses

    Josh Delekta

      Hello,

       

      Please see attached. I am trying to map multiple name fields and addresses. I have been trying to figure out a way to take "Name", "Name1" and "Name3" and consolidate into one "New Name" field as well as take "Address", "Address1" and "Address2" and consolidate into one "New Address" field.

       

      I don't think there is a way to plot three different name columns and three different address columns on the same map which is why I'm looking for ways to consolidate. Any thoughts?

       

      Data looks like this now:

      I think it should ultimately look like:

       

       

       

      Thank you.

        • 1. Re: Mapping Multiple Names and Addresses
          Simon Runc

          hi Josh,

           

          I've used the following formula to pull out just the Non-Null entry from each of the 3 columns (for both Name and Address)

           

          [New Name]

          IFNULL([Name],'')+IFNULL([Name1],'')+IFNULL([Name2],'')

           

          [New Address]

          IFNULL([Address],'')+IFNULL([Address1],'')+IFNULL([Address2],'')

           

          Hope that helps

          • 2. Re: Mapping Multiple Names and Addresses
            Josh Delekta

            Simon,

             

            Thanks for your quick response. I wasn't entirely clear with the second image I provided. I am not trying to combine the fields but rather union them. I've done the union in SQL but when I join my data with the new table and create the join I run out of space on the sql server so I was looking for an alternative way to produce what I need. Any other thoughts?

            • 3. Re: Mapping Multiple Names and Addresses
              Simon Runc

              hi Josh,

               

              Just so I'm clear

               

              ...you have 3 tables in SQL, which you union in SQL (as a view?) and then bring this into Tableau?

               

              Are you then joining another table to this view? (assuming it is a view) in Tableau?

               

              How many rows is the Union'ed table? and how many on the Table you are joining?

               

              If your 3 tables were nicely organised into a single table (with one name and one address, per person), how many rows would there be?

              • 4. Re: Mapping Multiple Names and Addresses
                Josh Delekta

                Simon,

                 

                I have 6 tables total that I am joining from SQL. Names and addresses come from 3 tables. There are different types of names and addresses which is why they come from fairly different table types. In SQL I have created a new table where I am taking the fields that I need and union'ing them in the name table. I'm then joining that table to the original six in order to plot all name and address types on one map while also maintaining the relationship between the tables so if I select a name type on the main dashboard it shows me that name as well as the names associated with it as well.

                 

                A neatly organized table which I've created has 15,000 rows but because I need the additional relationships the extract I populate is roughly 800,000,000. I've been researching for a bit and have seen some information around dual axis maps but I haven't seen anything around being able to drop 3 different name types and their corresponding addresses on the same map. Like my example above:

                 

                Mike is in Table 1 and has a Billing Address (Table 1) and a Mailing Address (Table 1). I would like to plot both.

                Nick is in Table 2 and has a Mailing Address (Table 2). I would like to plot that.

                Steve is in Table 3 and has a Mailing Address (Table 2). I would like to plot that as well.

                 

                That is at a high level while I'm working with. 3 tables of names and 2 of those 3 tables have addresses. In the case of table one there are 2 addresses.

                 

                Thank you for all your help.

                • 5. Re: Mapping Multiple Names and Addresses
                  Simon Runc

                  hi Josh,

                   

                  ....the extract I populate is roughly 800,000,000

                  Yes that'll certainly cause problems for all but the most exotic databases!!

                   

                  So if you only have 15k people whom you want to plot, even if we have 2 addresses for some we shouldn't need more than 30k rows. What mapping field are you using? are they all post/zip-codes or is their a mixture of types? If they are of the same type, we can use the dimension to plot both Mailing and Delivery on the same axis (so not needing to dual). I've attached a quick example on this data

                   

                   

                  Where I can just use the Postcode to map item, and then the Level of Detail on Address Type and CustomerID to get a mark per CustomerID/Address Type combination.

                   

                  What fields are you joining on (and are you using an INNER Join?)...as it seems like you are creating a lot of duplication (even all 15k rows joined against themselves would only be 225M rows, I say only!! but this is still crazy big and we shouldn't need more rows than marks)

                  • 6. Re: Mapping Multiple Names and Addresses
                    Josh Delekta

                    Simon,

                     

                    Per my initial description, the addresses exist in different columns which is part of the reason why the rows have grown exponentially. My data looks like:

                     

                     

                    The "Name" field can be associated with "Billing", "Shipping" or both. The "Name 2" and "Name 3" field is associated with "Mailing" but "Name 2" and "Name 3" exist in different tables. I need to plot all three name types and address types. This is my join which are all right joins:

                     

                     

                    I ultimately need all the relationships to populate in the dashboard because I need "Name" from table "A" to be associated with "Name 2" from table "C" and so on. The bottom table ("S") is something I created with SQL where I re-create all the joins in the SQL database and drop all the names into standardized "Name" and "Address" columns that I was hoping to plot on the map while maintaining the relationships I need in the cross tabs in my dashboard. I can plot them as expected but I'm at 800,000,000 rows so its not really useable. That unfortunately took my row count to the next next level.

                     

                    Any additional thoughts would be greatly appreciated. Thank you!

                    • 7. Re: Mapping Multiple Names and Addresses
                      Simon Runc

                      hi Josh,

                       

                      OK I think I understand. I'm a bit pushed for time today/tomorrow, but I'll take a look over the weekend. This is more a SQL/Data-modelling problem (which I also enjoy!), so I'll create a mock up of the situation and see if I can come up with a way to join it all up without generating near 1 billion rows!)

                      • 8. Re: Mapping Multiple Names and Addresses
                        Simon Runc

                        hi Josh,

                         

                        Having thought about this I think we can do it all by UNIONing and not joining. I've mocked up a quick 3 table Excel, which (hopefully) represents (as simplified) version of your data (I've attached this).

                         

                        So by using custom SQL and the UNION ALL I think we can get the data in the shape we need.

                         

                        So I have one Table called "Table 1 - Name1 + B_S", which looks like this....

                         

                         

                        I can then use the UNION ALL to bring this Table in twice, one bringing me back the Billing address and the other part to bring in the Mailing address. Notice how I need to ensure the output we are creating is the same for each UNION ALL, and just NULL out the bits that we don't need (or aren't in a table). The below image shows the output for this....

                         

                        I then also have 2 further tables

                        "Table 2 - Name2 + M" which looks like this...

                         

                        and "Table 3 - Name3 + M" which looks like this

                         

                        I can then continue with the same logic, as before and extend it to include these table too...

                         

                        SELECT [Table 1 - Name1 + B_S$].[Name] As [Name],

                        [Table 1 - Name1 + B_S$].[Billing Post Code] As [Billing],

                        NULL As [Shipping],

                        NULL As [Mailing],

                        "Billing" As [Address Type]

                        FROM [Table 1 - Name1 + B_S$]

                         

                        UNION ALL

                         

                        SELECT [Table 1 - Name1 + B_S$].[Name] As [Name],

                        NULL As [Billing],

                        [Table 1 - Name1 + B_S$].[Shipping Post Code] As [Shipping],

                        NULL As [Mailing],

                        "Shipping" As [Address Type]

                        FROM [Table 1 - Name1 + B_S$]

                         

                        UNION ALL

                         

                        SELECT [Table 2 - Name2 + M$].[Name] As [Name],

                        NULL As [Billing],

                        NULL As [Shipping],

                        [Table 2 - Name2 + M$].[Mailing Post Code] As [Mailing],

                        "Mailing" As [Address Type]

                        FROM [Table 2 - Name2 + M$]

                         

                        UNION ALL

                         

                        SELECT [Table 3 - Name3 + M$].[Name] As [Name],

                        NULL As [Billing],

                        NULL As [Shipping],

                        [Table 3 - Name3 + M$].[Mailing Post Code] As [Mailing],

                        "Mailing" As [Address Type]

                        FROM [Table 3 - Name3 + M$]

                         

                        btw even if the fields are called different things in different tables, this would still work fine, as we can use each name in each part of the UNION.

                         

                         

                        The data then looks like this...

                         

                         

                        Once I have all this, we can use the original formula to pull all the postcodes into a single field (we could actually have done this in the union, but wanted to make it clear what everything was doing)

                        [PostCode]

                        IFNULL([Billing],'')+IFNULL([Shipping],'')+IFNULL([Mailing],'')

                         

                        and then use [Name] and [Address Type] on the detail (I've added [Address Type] to colour), so get all the marks we need.

                         

                        Hope that helps, and makes sense. Let me know if not, and where it breaks down and we should be able to adapt the method.

                        1 of 1 people found this helpful
                        • 9. Re: Mapping Multiple Names and Addresses
                          Josh Delekta

                          Simon,

                           

                          Apologies on the delayed response. This worked perfect. Thank you!