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

# Join on Text columns

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.

Thanks,

Vamsikrishna.

• ###### 1. Re: Join on Text columns

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

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

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.

Sincerely,

Vamsikrishna.

• ###### 4. Re: Join on Text columns

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

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

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 3 FINANCE,DC,BILLER 4 BILLER,EPAY 5 FINANCE,FRS,CUS 6 CUS,EPAY 7 FRS,BILLER,EPAY 8 DC,FINANCE,FRS 9 BILLER,DC,BANKSOL 10 BANKSOL,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

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