4 Replies Latest reply on Jul 22, 2013 7:55 AM by Jim Wahl

    Is a data blend what I want (MS ACCESS)?

    Neal Smoller

      So I use MS Access as my data source, so therefore I can't do distinct counts without extracts in tableau.  I need to be able to refresh without taking lots of steps, so I was trying to do a query in the Access db that pulls the following:

       

      Date - Store - Number of Unique Invoices

      5-25-11   -  Store A - 63

      5-26-11   -  Store A - 45

      5-25-11   -  Store B - 15

      5-26-11   -  Store B - 27

       

      I have the query and all the data is correct.

       

      i was going to blend the data in tableau.

       

      On my worksheet, i have the attached image, which is basically a bar graph with sales on Y axis and day on the x axis, for past 7 days.

       

      I wanted the unique number of invoices for the each day as a label above the bar.

       

      Is a blend what I want?  It's not working over here...  even using the reference line example from the knowledgebase...

        • 1. Re: Is a data blend what I want (MS ACCESS)?
          Jim Wahl

          Hi Neal,

           

          A blend should work well here.

           

          I suspect the issue is that the date fields are not matching. Try changing the field type for InvDate in the secondary data source to Date (or DateTime, if that's the type in the primary).

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Is a data blend what I want (MS ACCESS)?
            Neal Smoller

            Jim Wahl,

            Thanks.

             

            I changed those two to have matching date formats.

             

            Still not working.  I'm wondering if the store field would be the culprit?

             

            I could email you a packaged wkbk if you'd like...

            • 3. Re: Is a data blend what I want (MS ACCESS)?
              Jim Wahl

              Needless to say, all the linked fields need to match. I'm happy to provide another set of eyes if you want to send me a twbx at jim@broadbandmetrics.com. I'm in Europe now, so my day is over, but I'm happy to take a quick look tomorrow morning.

               

              Jim

              • 4. Re: Is a data blend what I want (MS ACCESS)?
                Jim Wahl

                Hi Neal,

                 

                The extracts worked.

                 

                I think what's happening is the date / time field do not match in the

                initial blend because of the time stamp.

                 

                However, if you unlink the InvDate in the secondary data source the blend

                works, and then (somewhat oddly) you can reapply the link and it still

                works---seems like Tableau applies the truncated date that's in the view to

                both data sources.

                 

                Since you have the date fields in the view, you don't really need to have

                the InvDate link. Tableau will lookup all the matches based on StoreID and

                then partition the results by the dimensions in the view.

                 

                You'll run into a problem if you don't have dates in the view, but want to

                filter on dates. For example, in Sheet 14, I just have a single bar and use

                a date filter to show just the last 7 days. If the InvDate field is not

                linked, all dates will be returned and displayed.

                 

                You could fix this second problem and perhaps make everything more robust

                by creating a calculated field in each data source that truncates InvDate

                to the day level and then link on this field.

                DATETRUNC('day', [InvDate] )

                 

                 

                Jim

                 

                 

                 

                 

                 

                On Thu, Jul 18, 2013 at 9:28 PM, Neal Smoller <