7 Replies Latest reply on Sep 19, 2018 7:36 PM by Ankit Bansal

    Join on Text columns

    Vamsi Immadisetty

      Hi Gurus,

       

      I was trying to connect two tables considering two text columns please help me how to work on it.

       

      Table 1:

      Col 2

      AAA, BBB,CCC

      BBB, CCC, DDD    

      CCC,DDD,EEE

       

      Table 2:

      Col 1

      A

      B

      C

       

      Join condition would be if Table 2.Col1=A THEN Table1.Col2=AAA else if Table 2.Col1=B THEN Table1.Col2=BBB else if.....

      Luckily we did not have combinations otherwise we might end up so many permutation and combination.

       

      Please help how to achieve this in tableau

       

      Thanks,

      Vamsikrishna.  

        • 1. Re: Join on Text columns
          Ankit Bansal

          Not sure what you are trying to do.

           

          Are you trying to derive col2 based on col1, what join you want to do here?

          • 2. Re: Join on Text columns
            Simon Runc

            hi Vamsi,

             

            So assuming your real world situation reflects the above, you can use the Join Calculation option

             

             

            where you could join from Table 1 Join Calculation

             

            LEFT([Col2],1)

             

            with

             

            Col 1 from Table 2.

            • 3. Re: Join on Text columns
              Vamsi Immadisetty

              Hi, good to see your response.

               

              I was actually trying to create multiple data sources with defined join condition like

              Data source 1 with all data where Table1.Col2 contains "AAA". We do have ten segments like this. We thought of create ten different tables and connect but I was looking for the option if there any dynamic option if I select A in Table2.Col1 the we get only data related to AAA.

               

              Appreciate for your help.

               

              Sincerely,

              Vamsikrishna.

              • 4. Re: Join on Text columns
                Ankit Bansal

                Vamsi,

                You can do something like this:

                 

                 

                You can write join condition like below for table 2:

                 

                if [Col1]='A' then 'AAA'

                elseif [Col1]='B' then 'BBB'

                and so on...

                end

                 

                and from table 1 just choose col2 for join.

                 

                Hope this helps.

                 

                Thanks,

                Ankit Bansal

                • 5. Re: Join on Text columns
                  Simon Runc

                  So yes if there is a "pattern" you can define (such as in your example where 'A' is the first character of the join string 'AAA'), or if more complicated, and you only have 10, you could create an IF Statement as Ankit has shown

                  • 6. Re: Join on Text columns
                    Vamsi Immadisetty

                    Hi Ankit,

                     

                    I have tried to do the same as you mentioned but first of all I was not able see the custom join option 'Create Join Calculation'. I am using Tableau 10.0.0 version.

                    To make it clear, I was mentioning accurate table data and the scenario what I am looking for.

                     

                    TABLE_DIVISION

                    Division
                    EPAY
                    DC
                    FRS
                    BANKSOL
                    CUS
                    BILLER
                    FINANCE

                    TABLE_BUREFERENCE:  

                    CLIENTID   DIVISION
                    1  DC,EPAY,FRS
                    2  EPAY,FRS,FINANCE
                    3FINANCE,DC,BILLER
                    4BILLER,EPAY
                    5FINANCE,FRS,CUS
                    6CUS,EPAY
                    7FRS,BILLER,EPAY
                    8DC,FINANCE,FRS
                    9BILLER,DC,BANKSOL
                    10BANKSOL,EPAY,DC

                     

                    Here I wanted to create join between Table 1.Division = Table 2. Division like     

                    if  Table_Division.DIVISION = 'EPAY' THEN Table_BUREFERENCE.Division contains 'EPAY'

                    if  Table_Division.DIVISION = 'DC' THEN Table_BUREFERENCE.Division contains 'DC'

                    if  Table_Division.DIVISION = 'CUS' THEN Table_BUREFERENCE.Division contains 'CUS'

                    and so on...

                     

                    How I wanted to use this in reporting

                     

                    In reporting I will be using Table_DIVISION.Division as Filter and created couple of objects considering Table_BUREFERENCE.

                     

                    When I select DC in filter all charts show data with respect to DC and when in select FRS in filter then all charts show data with respect to FRS and similar with other selection options.

                     

                    I try to import two tables as two individual data sources but still not able to connect both with no common column

                     

                     

                     

                    Please advise me how to proceed, it was so important for us to go forward.

                     

                    Sincerely,

                    Vamsi

                    • 7. Re: Join on Text columns
                      Ankit Bansal

                      Ok this feature is available only from 10.2 version onward.

                       

                      May I know what is your data source? excel, database? Because somehow we need to join 2 data sources. If possible we at least need to create a dummy column in both the sources with value just 1 for all the rows. After that we can do workaround to achieve the scenario.

                       

                      Thanks,

                      AB