Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please some explain me
Difference between join and concatenate
when should each command be used ?
what is more effective ?
Thanks in advance
PFB..
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;
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.
JOIN and CONCATENATE are both very useful and frequently used functions in Qlikview. It's important to understand the differences between them.
-Rob
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
Hi,
I have attached images to help you explain the concepts. Please view them. I hope it helps.
PFB..
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;
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.
JOIN and CONCATENATE are both very useful and frequently used functions in Qlikview. It's important to understand the differences between them.
-Rob
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
Thanks lot for descriptive explanations. I\