CODEWITHSUNDEEP
tableau-apitableau-desktop
Yige Song
Yige Song

Reputation: 373

Tableau: relationship between tables with different fields

I am really new to Tableau.

I have an "accident" table (excel) that describes each traffic accident in the past few years, including its "district" (location). I have another "district" table that describes each district of the city, including its population.

Now I want to join those two tables and create a graph of accidents per person for each district.

The problem I face is: The two excel files are from different databases, which means that the same district may appear to have different names in two tables. How do I let tableau know the matching between districts?

Could you tell me how I can join those two tables so that I can create my chart?

Please let me know if there are any problems with my approach or understanding. Thank you in advance!

Upvotes: 1

Views: 150

Answers (1)

dbunk
dbunk

Reputation: 16

Assuming I understand question correctly. Your accident transactions might have.. District#1, Accident Date, , District#2, Accident Date, .... District#3, Accident Date, .... Your district dimension table has 1 unique record per district, but names don't match. District1, CityName District2, CityName District#3, CityName

You want to combine the results correctly... Excel is the preferred datasource? I assume the actual sources of data do NOT have a singular "code" value for the district name that actually does match. This is where MDM is important for system integrations. Right answer IMO is to actually have source systems/dbms understand that they are integrated and create XREF in 1 system that is required when a district is setup or exists.

Options. I think you need to "clean" the data someplace. The question is where.

  1. MDM in your source system integrations.
  2. Are the excel sources created in automated fashion? You could create a manual XREF excel sheet and lookup/decode one of the district values to conform a single definition of the district. In addition to your graph. create an audit that looks for any new data that doesn't "lookup" correctly to know to maintain the manual XREF.
  3. Probably could create calculated XREF is tableau calc itself and then blend based on that calculated field. I think that would require full desktop versus web editor.
  4. Tableau Prep might help as well. (although my company doesn't use this)

Upvotes: 0

Related Questions