7 Replies Latest reply on Jul 22, 2018 9:11 AM by Jeevan Krishna

# Connecting sheets without joining

Hey everyone!

I am working on this attached data sheet (only ten rows shown). The goal is to filter out marine locations that are 100 miles away from selected cities. However, the  dataset is composed of two datasheets  but WITHOUT A common field to create a union or join.

I added both data sheets to Tableau separately using Add New Data Source, but cannot move forward without creating a relationship. I wonder if there is another way to filter locations within 100 mile and other locations.

• ###### 1. Re: Connecting sheets without joining

Hi Imesh,

Besides adding the 2 sheets as 2 different data sources, you can pull them together into a single datasource like below.

Tableau currently recognizes that the latitude fields are similar and tries to join on it.

But what you need to execute this task is you need a cross join and for each project you need to calculate distance with all cities and filter the cities by distance.

A cross join can be created as below.(Hope your data is not huge as cross join results in huge no.of records)

Add a column with value 1 in each of the sheets.

Re read the excel connections and connect as shown

This creates a cross join.

Hope this helps

1 of 1 people found this helpful
• ###### 2. Re: Connecting sheets without joining

Thanks, Jeevan! Your answer is clear, powerful and worked joining datasheets together.

Can you please give me some tips on  how to filter out locations that are 100 miles away from cities in the datasheet? Thanks in advance!

• ###### 3. Re: Connecting sheets without joining

Thanks Imesh.

Tableau has all the functions necessary. Should be easy to recreate.

Hope this helps

1 of 1 people found this helpful
• ###### 4. Re: Connecting sheets without joining

Thank you Jeevan!

This formula works perfect and the  distance between two points with Lat Long can be calculated easily.

However, I am still far behind moving forward to hit my goal.  In my case, I have 100+ locations in the ocean and have 25+ cities on the land (map attached). Cities on the land are major points and the ultimate goal is to show the distance from cities to ocean locations.

First I was thinking of  creating buffer zones around each city using some threads but it eventually makes the whole dataset  into an enormous number of rows.

I am wondering if I can put a circle with 100km for each city. Would that work when zooming in and out options though?

Any thoughts appreciated.

• ###### 5. Re: Connecting sheets without joining

Hi Imesh

I'm not sure how I can be of help in reaching your end goal.

But I couldn't stop appreciating the colors and layout of map, which is very well done. Is it a custom map box chart or you achieved this with the map layers alone? Can you please share how this can be done?

• ###### 6. Re: Connecting sheets without joining

Hi Jeevan,

Thank you!!

Mapping part is very simple. I learned it from a Tableau video lesson.

Resources: There are many cool maps on the service.

How to do it in Tableau

1:  Create a basic map using country/city/lat/long and etc.

2: Go to Map > background maps > Map Services

3: Map service box will open > Add > WMS servers

4: Paste the above link to global map/ or any other map from the WMS website

5: It will load a map with several layers.

6: Go back to Map > Map layers > then select layers you'd like to have on your viz.

There was a tableau video about it but did not come up on my search today.

• ###### 7. Re: Connecting sheets without joining

Hi Imesh,