join

[#1]

how natural join work in qlikview?

[sivarajs]

A simple join will combine all the matching fields from two tables

sivaraj s

[#3]

no m asking about NATURAL join..... not Inner Join.....is der ny differencr?

[sivarajs]

this a natural join, this can be preceded by inner ,left,outer

[Jason_Michaelides]

A natural join (JOIN with no prefix) will look at all fields in both tables and join rows where all the common fields match. The unmatched rows will be concatenated to the table (added to the bottom).

For (a simple) example:

Table A has 100 rows and fields A,B,C and D

Table B has 100 rows and fields A,B,E and F

20 rows in both tables have the same values for A and B. The other 80 rows are different.

Data:

LOAD * From A...;

JOIN (Data)

LOAD * From B...;

The table "Data" will have 180 rows and fields A,B,C,D and E.

- The first 80 rows are the rows from A that don't match rows in B.

- In these 80 rows, E and F will be NULL

- The next 20 rows are the rows common to the two tables. A,B,C,D and E will all have values (assume the source data has values)

- The final 80 rows are the rows from B that don't match rows in A.

- In these 80 rows, C and D will be NULL

Hope this helps,

Jason

[#6]

if those 20 rows have some data common and some different data then will it work?

[Jason_Michaelides]

If all the common fields (A and B in this case) have the same data in them then it doesn't matter what the other field values are - these rows will join together.

[#8]

but if A,B have some different data  .....means A have some data which is not present in B then? will they join togather n if yes den how?

[Jason_Michaelides]

I'm not sure what you don't understand, I'm afraid.  Let me try again...

Table1:

A	B	C	D
1	xyz	321	JH
2	wew	546	kj
3	huhu	77	jknj
3	xyz	741	iii
2	plo	98	uiy

Table2:

A	B	E	F
1	xyz	ssda	ed423
2	wew	xcvxc	kj22
4	huhu	3ed	1123
5	xyz	rtere	da
9	plo	liugf	erwwe

JOIN (Table1) LOAD * FROM Table2...;  will give

The common fields are A and B.  Rows 1 and 2 have identical values for A and B therefore these rows from both tables are joined together.  The other rows don't have identical values for both A and B (i.e. all the common fields) so these become distinct rows in the new table.

Notice that 2 tables of 5 rows each have become a single table of 8 rows. 2 common rows plus 3 distinct rows from Table1 plus 3 distinct rows from Table2.

Hope this helps,

Jason

[#10]

ok..... thanx..... i got it....