Join and Concatenate

upaliwije — Sat, 02 Nov 2013 06:01:41 GMT

Please some explain me

Difference between join and concatenate

when should each command be used ?

what is more effective ?

Thanks in advance

Re: Join and Concatenate

Sat, 02 Nov 2013 07:54:09 GMT

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

Re: Join and Concatenate

Sat, 02 Nov 2013 08:08:34 GMT

Hi,

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

Re: Join and Concatenate

Umesh — Sat, 02 Nov 2013 09:07:54 GMT

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

Re: Join and Concatenate

sasikanth — Sat, 02 Nov 2013 09:30:23 GMT

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

Re: Join and Concatenate

upaliwije — Sun, 03 Nov 2013 07:41:42 GMT

Thanks lot for descriptive explanations. I\

topic Re: Join and Concatenate in QlikView

Join and Concatenate

Re: Join and Concatenate

Re: Join and Concatenate

Re: Join and Concatenate

Hi Upali,

Understanding Join and Concatenate

Re: Join and Concatenate

Re: Join and Concatenate