topic join in QlikView

join

Wed, 28 Mar 2012 05:57:01 GMT

how natural join work in qlikview?

join

sivarajs — Wed, 28 Mar 2012 06:42:12 GMT

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

sivaraj s

join

Wed, 28 Mar 2012 07:38:25 GMT

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

join

sivarajs — Wed, 28 Mar 2012 07:46:18 GMT

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

join

Jason_Michaelides — Wed, 28 Mar 2012 08:01:12 GMT

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

join

Wed, 28 Mar 2012 08:16:55 GMT

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

join

Jason_Michaelides — Wed, 28 Mar 2012 08:31:05 GMT

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.

join

Wed, 28 Mar 2012 08:51:11 GMT

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?

Re: join

Jason_Michaelides — Wed, 28 Mar 2012 09:46:52 GMT

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

join

Wed, 28 Mar 2012 10:15:36 GMT

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

join

Jason_Michaelides — Wed, 28 Mar 2012 10:17:29 GMT

Cool. Please mark a correct answer so others can see it's resolved.

Re: join

Sat, 06 Apr 2013 06:35:49 GMT

Thank you Jason ! Its very clear!