#Join

Two ways to combine two different tables –

1 – Appending – vertically combining the data joining as rows

adding the table as extra rows.

vertically combining the data is called Union.

If column heading is same, then only we should join.

If there are mismatch of column heading, then it will be created as separate column as extra number of column.

Null value will be shown for the heading with no relevant value related to the table. 

position of column doesn’t matter, but the matching of heading is important.

*********

2. – 

How to join or append the data

double click on table after dragging to data source area.

then drag the second table to union and then leave it below (not to the right) the first table.

Once the union is done, it will show the hierarchy symbol to the left of the name of the table.

while dragging it will show “drag to union” below the first table.

We can rename the combined table to avoid confusion and not count it as single table.

If you drag the column or dimension with null value, it will show null value in the chart.

There are formulas to fill null values.

ZN(sales) – used for null value calculation (need to research futher)

Ifnull(brand,”no brand”)

************************************

Join

Different tables are kept separately to manage the memory.

All types of columns are not combined, because it will increase the size of the database with irrelevant values in the irrelavant column.

It is a little bit of data engineer concept, which should be known by the data scientist or data analyst.

example, age column may not be required in the transaction table. Whereas sale column is must in the transaction table.

The database or table should be also optimized in terms of size also. Unneccessary size increase should not be there.

We will use join to combine

There 4 types of Join

1. Left Join

2. Right Join

3. Inner Join

4. Outer Join

There are more types of joins, but those are not much used.

1. Left Join – 

Bring all rows from left table, bring only matching rows from right table.

It is called left Join. It means full rows of left and only matching rows from the right table. unmatched rows will not be taken from right table.

2. Right Join – It is the inverse of left table.

all rows in the right table will be there(whether matched or not), all matched rows from the left table (unmatched will not be taken from left or skipped.)

***********

3. Inner Join

Only matching rows in both left and right will be shown.

unmatched rows from both table will not be shown.

Inner join will only shows common rows, common data. – common order id.

******************

Outer Join – everything included

combined all common and uncommon

combine all rows in left and right table, whether combined or not.

***************

Also, can we only find uncommon rows using the join?

we will need to apply filter on right and left join with Null.

It will pickup null value from both right and left table. and you can see the unmatched or uncommon value.

***********

Union is vertical adding and join is horizontal adding.

**********************************

full outer

How to join

Drag to the right of the 1st table after double click.

by default, tableau will show inner join means it will only show common rows. it can be changed according to our requirement such as left, right, full outer.

You can change the join type by clicking on it. 

you can also match the heading manually, it can be used when there is spelling mistake such as product and prduct.

you can also add more matching heading which makes sense, 

**************

color division in the bar can be changed by dragging the color from top to bottom in the legend. The info will be shown like that in the bar also, in that sequence.

**************

Join combine at row level, 

Union combine at column level

*********

three table or multiple tables can also be joined

*******

Blend

it is used to combined data from different data sources such as excel, ms sql, oracle, saleforce etc.

difference b/w bledn vs join

1. blend aggregate first and then combine the data, 

join combine the data first, then aggregate it.

2. join can’t combine two different data set.

******

How to blend

1. open the first file and drag the table

2. click on the cylinder symbol (new data source), then open 2nd file,

drag the desired table from 2nd table.

3. find the common heading and create connection by clicking on blend relationship from . (Click on Data – Edit Blend Relationship)

it can automaticly connect common heading. otherwise you can manually connect it.

once common dimension relationship is created, it will show chain symbol next to the dimension in the list.

Drag the common dimension from first table to the chart. 

go to second table and select another dimension to chart.

************

primary will show in blue, secondary will show in orange color for file name. Also, in the rows/column shelf, secondary will show ass orange color.

**********

blend is by default – left join

there is no right join concept, we can only swap between primary and secondary table.

***********************

United States – USA

How to correct and match same item but with different name.

we can use alias.

#combined chart can’t be created using common dimension/measure.

#We can’t create two exact same alias. we can use formula, –

If (country) IN == ‘united states, ‘USA’,’United States of America’, then ‘usa’ end.

This can be used to clean the data, where there are more than 2-3 different names of usa, but we want to combine them and show as single value.

******************

Blending – First aggregate, then show in chart. 3 same order id will be combined and shown in 1 row. 

Join – First combine and then aggregate in chart. 3 same order ids will be shown in 3 different rows.

#blend – it is used to do calculation from different data sources.

#ignore table calculation – table calculation will not be used.

*******************

Different data type source – blend

same data source type – join