1 Reply Latest reply on May 2, 2017 12:03 PM by Joshua Milligan

    join two table

    A Saeedi

      hi,

      i have two table in excel (t1,t2).

      how to join to two table and create total sheet?

        • 1. Re: join two table
          Joshua Milligan

          A Saeedi,

           

          It's not entirely obvious to me what behavior you expect because it looks like you have values like bmw, bmw1, bmw2 that you want to collapse into "bmw".  And I'm not sure how that will work across all the possible values of your data, but assuming that it's as simple as dropping off the numeric part of the description, here's an approach I might try:

           

          1. Create a Union of the two tables:

           

          That will give you a Name field along with the value1 and value2 fields (also Sheet and Table Name, but you can hide those if they are not useful).

           

          Then, I'd create a calculation to strip off the numeric part of the name.  One possibility is to use a regular expression.  So Name Fixed has the code: REGEXP_EXTRACT([Name], '([a-z]+)')

           

          At this point, I can easily recreate the totals you had above:

           

           

          Hope that helps!

          Joshua