4 Replies Latest reply on Mar 20, 2013 10:36 AM by Lilliane Costa

    Data Blending Quandary: "no linking fields" but they're both dates

    Leigh Fonseca

      I'm in a bit of a data blending quandary. 

       

      I'm trying to blend two data sources and I'm working with extracts.  One data source was extracted from Hadoop and the other from a DB.  I'd like to avoid importing either data set into the other source system if possible.

       

      Both extracts have a date field and a station_code field.  Station Code is referring to weather stations.  My primary extract contains sales data by zip code with a column that contains the corresponding weather station code. I'll refer to as Extract S (for sales).

       

      My secondary extract is weather data by station.  I'll refer to this as Extract W (for weather).  Both extracts are at the time grain of day even though I intend to look at the data monthly.  I cannot seem to get a sales metric (Extract S) and a weather metric (Extract W) on the same view.

       

      To simplify things, I'm just trying to put the date fields from both extracts on the same view and I get the dreaded error:

       

      "Cannot blend aggregated data from the secondary data source with the data on this sheet because there are no linking fields used in the view."

       

      The only fields used in the view are the date fields, to which I did a

       

      Data > Edit Relationships

      Primary Data Source: Extract S.  It has data from 2003-2012

      Secondary Data Source: Extract W.  It has data from 2010-2012.

       

      Relationship = Custom

      Fields mapped:

      MY(SLS Date) = MY(Weather Date)

      STATION_CODE = weather_station_code

       

      relationship issue 2.jpg

       

      I do not understand how there can be "no linking fields" when the only two fields on the view are MY(SLS Date) and my(Weather Date) and these two are mapped in the relationship editor.  Any ideas?

       

      Thanks,

      Leigh

        • 1. Re: Data Blending Quandary: "no linking fields" but they're both dates
          Tracy Rodgers

          Hi Leigh,

           

          The SLS Date on your columns shelf needs to be set to MY(SLS Date) since this is the granularity the relationship between the two fields is set to.

           

          -Tracy

          • 2. Re: Data Blending Quandary: "no linking fields" but they're both dates
            Leigh Fonseca

            Thanks, Tracey.  That fixed the date issue.  I'm still having a data blending issue with these two data source.

             

            Both data sources also contain a Station Code (for weather station code).  Both source contain the station code KABY but when I try to put these both on the same view I get no data.  This is puzzling to me because individually I can display this code when limiting the view to either one of the data sources and I do not get an error that there are no linking fields.

             

            Here is a screen shot showing the relationships.  GEO_SLS is my primary and I placed that on the view first.  Then I added the Weather History Station code and the results are null.   Noticed that the first GEO_SLS Station Code is KABY.  This station is also present in the Weather History data (see below) but when I add Weather History.Weather Station Code to the view I see null for all entries.

            Cannot Blend Station Codes.jpg

            When I only have Weather History on the view you can see that there exists a Weather Station Code KABY.

             

            kaby.jpg

             

            Ultimately, I'm trying to look at Sales & Weather Data by Month and DMA (DMA is in GEO_SLS and accessible by linking Weather Station codes from both data sources).  Ultimately I'm trying to geomap these trends by DMA which have specific zipcodes.  Can you please tell me what I'm missing with the Station Mapping?

             

            Additional Note: I just exported to crosstab the station code data from Weather and Sales and then included either a sales metric or a weather metric from the Tableau workbook for the MY(August 2011).  From Excel I then imported two worksheets and bound the relationship on station code.  In the new workbook I was able to create a view with both a sales metric and a weather metric for Aug 2011.  No other dates were imported.  This confirmed for me that there is data for this time period that can be mapped based on station code.  I feel like I'm so close.  Your help is much appreciated!!

             

            Thanks much in advance!

            Leigh

            1 of 1 people found this helpful
            • 3. Re: Data Blending Quandary: "no linking fields" but they're both dates
              Leigh Fonseca

              Thanks to Tableau support for helping me with this one.  I thought I'd post an update in case someone runs across a similar problem where they know their blending fields contain matching data, and their data is at the right grain but the dataset returned is null.

               

              The problem was that there were spaces in my Weather Station Code fields.  When I created a calculated field "New Weather Station Code" as trim([Weather Station Code]) for both data sources, and removed the previous automatic relationship for "Weather Station Code"s the data blending worked like a charm. 

               

              Thanks again TSCS!

              • 4. Re: Data Blending Quandary: "no linking fields" but they're both dates
                Lilliane Costa

                Thanks Leigh!  I was having the same problem and this solved it!