1 Reply Latest reply on Dec 14, 2016 11:14 AM by Santiago Sanchez

    Trim before joining

    Brad Estep

      Hello,

      I have two data sources and I'm joining on Invoice#, the issue is that it is not joining on entries that have blank spaces, or commas, for instance:

      14.326 will not join to 14.326 on the other source, they are both set to string in tableau, another example is "FreshAir Conference" won't join to "FreshAir Conference" on the other data source, due to the space I'm guessing.  Does anyone know a way to trim the fields before joining?  I had thought of using the ms access jet sql language that tableau has available to perform the join, but the last time I tried that you couldn't use filters while doing that.  Thanks in advance

        • 1. Re: Trim before joining
          Santiago Sanchez

          Hi Brad,

           

          Depending on the data source you are using, you may be able to use Custom SQL to do this. Connecting to Excel, for example, you can use the legacy connector (the jet driver you mentioned above) and you can do something like this:

           

          SQL1.png

          SQL2.png

          JOIN.png

           

           

          TRIM is supported on that driver so you can define a trimmed column for each data source, then use it to make the join. The filter section on either the data source window or the sheets is still available!

           

          Also, a BETA version of Tableau 10.2 was just launched and one of the features Coming Soon is called 'join calculations'. That may be useful as well! You can join the BETA program in that same page.

           

          Hope this helps!