4 Replies Latest reply on Dec 19, 2017 7:52 AM by Eric Soderlund

    Blend vs. join vs. LOD - How to replace null values in one table with 0s in the other table

    Eric Soderlund

      Hi Tableau stars,

      I am struggling with something that I think should be really easy, but have thought myself into a corner using Tableau and need your help getting unstuck.

       

      Here’s the situation: I have two tables in the same data source, Table 1 contains a Location ID, geographic coordinates (Lat and Lon), and the year; there are no duplicate records in Table 1--everything is unique for LocationID and year combos. Table 2 has the Location ID, Product Name, Year, and a Product Value (specific for that year at that location ID). All values in Table 2 have a corresponding record in Table 1, linked by Location ID and Year. There can be many records in Table 2 for a single record in Table 1. But not all Location IDs in Table 1 have a record for all Product Names available in Table 2. That is, the Product Name list is large and only a small number of those products are available at each Location ID.

       

      Goal: Users can select a Year and Product Name. I want to display all records from Table 1 for a selected year and the Product Value from Table 2 based on a user-selected Year and Product Name. When there is not a corresponding record in Table 2 at a Location ID in Table 1 for a Product Name and Year combination, I want  use '0' for the Product Value for that Selected Product Name. The end product will be a map showing the Product Value at all Location IDs for that Year in Table 1, including displaying a '0' for locations that do not have corresponding record for that Product Name.

       

      My current attempts all filter out Location Ids from Table 1 when I filter for a Product Name because when I filter on Product Name, all LocationIDs without a corresponding record in Table 2 are filtered out of the view. This is where I am stuck.

       

      There are about 1,200 Location IDs in each of the 20 years of data in Table 1 and roughly 200 different Products  are available at each LocationID in Table 2.

       

      I see that Tableau 10.2 has Join calculations and wonder if that may be a good starting point. I have no experience with join calculations... Any pointers are would be most welcome.[

      Thanks

      Eric