2 Replies Latest reply on Feb 11, 2020 1:40 PM by Michael Gillespie

    Join not matching SQL results?

    Matthew Stone

      We are working on matching on a license ID which is unique and located in two tables in our DB. When we do a match in SQL we get 1.2m matches. When we do that same match with an inner join in Prep, we only receive 319k matches with an additional 719k exclusions. Any thoughts on why Prep is not matching on all of the license IDs?

        • 1. Re: Join not matching SQL results?
          Jonathan Drummey

          Hi,

           

          I'm trusting that the flow in Prep has basically 3 steps (an input for each table, and the join step). Given that I've got a few thoughts:

           

          1) Is Prep doing any sampling? If you're seeing the Sampled indicator Configure your Data Set - Tableau then Prep is reducing the output data set while you're in interactive mode. If you put in an Output step and run the flow it should generate all the expected results. (Note that Prep does have some hard coded limits, see the docs I referenced for details).

           

          2) It turns out there's a an issue with sampling being always on in some cases in 2019.4.2 and 2020.1.2: https://kb.tableau.com/articles/Issue/sampled-data-is-showed-despite-selecting-use-all-data-in-tableau-prep?_ga=2.136764…, so that might be an issue here.

           

          3) You didn't say what data type you were joining on nor the particular raw data source...if you're using text fields there might be something going on with collation behavior. For example in a case-insensitive join then "that" and "THAT" will join, whereas if it's case-sensitive then "that" and "THAT" won't join. So I'm wondering in this situation if you're seeing a case-insensitive join in the database vs. a case-sensitive join in Prep. My experience with Tableau Prep so far (and from talking with the devs) is that Prep does its best to retain the collation behavior of the source data, however it might be that there's some manipulation or issue that's causing a difference.

           

          Jonathan

          • 2. Re: Join not matching SQL results?
            Michael Gillespie

            Prep samples the data unless you tell it not to.  Search the help for "disable sampling" for a discussion.