Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
upaliwije
Contributor II

Join and Concatenate

Please some explain me

Difference between join and concatenate

when should each command be used ?

what is more effective ?

Thanks in advance

Tags (1)
1 Solution

Accepted Solutions
Umesh
New Contributor III

Re: Join and Concatenate

Hi Upali,

PFB..

Understanding Join and Concatenate

The Qlikview script functions JOIN and CONCATENATE can sometimes be used to tackle the same problem, but there are important differences that should be understood.


Examine the sample tables below. Note that they share one common field name, "Key". Also note that Table1 has a Key value "3" that is not present in Table2.

JOIN will combine rows where the Key value matches. The keyword OUTER will also retain rows that do not match rows in the other table. Here's what the merged table will look like after an outer join.

OUTER JOIN (Table1) LOAD * RESIDENT Table2;


Values A1 and C1, which were in different tables, now occupy the same row in the result table. The row with Key 3 has missing values for C & D, because there was no matching Key in Table2.


Creating a chart that uses "Key" for dimension will produce results similar to the Table Box above.



The important point is that values with the same Key value have been merged together into a single row. If value A1 is selected, note that values C1 & D1 remain associated (white). The set A1,B1,C1,D1 is indivisible.

Now let's look at Concatenate. Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows from the two input tables. Here's what our sample data will look like after Concatenate.

CONCATENATE (Table1) LOAD * RESIDENT Table2;


Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the "other" table.

If the data is used to build a chart that utilizes the common field "Key" as dimension, the chart looks just like the JOINed table.

Let's make the selection "A1" in Field A and see it's impact on our visible charts and tables.


When A1 is selected, the association to C1 & D1 is lost and C/D values become null in both the Chart and Tablebox. We cannot select both A1 and C1. This is a different result than the JOINed example.


Let's consider a more realistic example where we may choose between JOIN and CONCATENATE. Consider the two tables below. Note that only one BudgetAmount row is present for each Region-Year combination. In the Sales table, the SalesAmount is broken down by Department within Region.

If we load both tables we can produce a chart using expressions like =Sum(BudgetAmount).


The Budget and Sales values have been summed correctly.


http://3.bp.blogspot.com/_IhQc1-zXHmU/SwYYvWFNfSI/AAAAAAAAAGQ/rv2R--o8pzw/s1600/SynKey.jpg

We then notice that we have an undesirable synthetic key, created by the Budget and Sales tables sharing the Year and Region fields. One approach to eliminate the synthetic key would be http://4.bp.blogspot.com/_IhQc1-zXHmU/SwYZ0bB6adI/AAAAAAAAAGY/FK4lnYG5hBE/s1600/SynKey.jpgJOIN or CONCATENATE. But which one in this case?



Let's try JOIN and see what the Chart looks like.

OUTER JOIN (Budget) LOAD * RESIDENT SALES


The summed Budget numbers are incorrect!


A look at raw data of the joined table will identify the problem. The JOIN repeated the BudgetAmount value on each Department row.


Let's try with CONCATENATE.


CONCATENATE (Budget) LOAD * RESIDENT Sales;


The numbers are now correct and we've accomplished the goal of eliminating the synthetic key.

A peek at the data in the Concatenated table will make it clear why the chart is now correct. There is only BudgetAmount value or each Year-Region.


JOIN and CONCATENATE are both very useful and frequently used functions in Qlikview. It's important to understand the differences between them.

-Rob

View solution in original post

5 Replies
Not applicable

Re: Join and Concatenate

concatenate append/add tables, loads to previously loaded tables. like

sql union but it will not check duplicate rows and concatanating tables could have different columns

on the other hand join will link diffrent tables based on linking fields (same field name and having same values in the fields) Join operation is identical with Joins in SQL

Not applicable

Re: Join and Concatenate

Hi,

I have attached images to help you explain the concepts. Please view them. I hope it helps.

Umesh
New Contributor III

Re: Join and Concatenate

Hi Upali,

PFB..

Understanding Join and Concatenate

The Qlikview script functions JOIN and CONCATENATE can sometimes be used to tackle the same problem, but there are important differences that should be understood.


Examine the sample tables below. Note that they share one common field name, "Key". Also note that Table1 has a Key value "3" that is not present in Table2.

JOIN will combine rows where the Key value matches. The keyword OUTER will also retain rows that do not match rows in the other table. Here's what the merged table will look like after an outer join.

OUTER JOIN (Table1) LOAD * RESIDENT Table2;


Values A1 and C1, which were in different tables, now occupy the same row in the result table. The row with Key 3 has missing values for C & D, because there was no matching Key in Table2.


Creating a chart that uses "Key" for dimension will produce results similar to the Table Box above.



The important point is that values with the same Key value have been merged together into a single row. If value A1 is selected, note that values C1 & D1 remain associated (white). The set A1,B1,C1,D1 is indivisible.

Now let's look at Concatenate. Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows from the two input tables. Here's what our sample data will look like after Concatenate.

CONCATENATE (Table1) LOAD * RESIDENT Table2;


Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the "other" table.

If the data is used to build a chart that utilizes the common field "Key" as dimension, the chart looks just like the JOINed table.

Let's make the selection "A1" in Field A and see it's impact on our visible charts and tables.


When A1 is selected, the association to C1 & D1 is lost and C/D values become null in both the Chart and Tablebox. We cannot select both A1 and C1. This is a different result than the JOINed example.


Let's consider a more realistic example where we may choose between JOIN and CONCATENATE. Consider the two tables below. Note that only one BudgetAmount row is present for each Region-Year combination. In the Sales table, the SalesAmount is broken down by Department within Region.

If we load both tables we can produce a chart using expressions like =Sum(BudgetAmount).


The Budget and Sales values have been summed correctly.


http://3.bp.blogspot.com/_IhQc1-zXHmU/SwYYvWFNfSI/AAAAAAAAAGQ/rv2R--o8pzw/s1600/SynKey.jpg

We then notice that we have an undesirable synthetic key, created by the Budget and Sales tables sharing the Year and Region fields. One approach to eliminate the synthetic key would be http://4.bp.blogspot.com/_IhQc1-zXHmU/SwYZ0bB6adI/AAAAAAAAAGY/FK4lnYG5hBE/s1600/SynKey.jpgJOIN or CONCATENATE. But which one in this case?



Let's try JOIN and see what the Chart looks like.

OUTER JOIN (Budget) LOAD * RESIDENT SALES


The summed Budget numbers are incorrect!


A look at raw data of the joined table will identify the problem. The JOIN repeated the BudgetAmount value on each Department row.


Let's try with CONCATENATE.


CONCATENATE (Budget) LOAD * RESIDENT Sales;


The numbers are now correct and we've accomplished the goal of eliminating the synthetic key.

A peek at the data in the Concatenated table will make it clear why the chart is now correct. There is only BudgetAmount value or each Year-Region.


JOIN and CONCATENATE are both very useful and frequently used functions in Qlikview. It's important to understand the differences between them.

-Rob

View solution in original post

sasikanth
Valued Contributor III

Re: Join and Concatenate

Hi

Concatenate is Just appending rows of one table to another table

NoConcatenate is also there

Join means combining both tables based on a common column ... We have diffnt types joins

1)Inner join

2)Left Join

3)Right join

In the above Hamman Tariq images are Good examples

Depends on the situation we can use them ...for example Go through Incremental load scenario you will come to know

When will we use both of them

upaliwije
Contributor II

Re: Join and Concatenate

Thanks lot for descriptive explanations. I\