Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
how natural join work in qlikview?
Cool. Please mark a correct answer so others can see it's resolved.
A simple join will combine all the matching fields from two tables
sivaraj s
no m asking about NATURAL join..... not Inner Join.....is der ny differencr?
this a natural join, this can be preceded by inner ,left,outer
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
if those 20 rows have some data common and some different data then will it work?
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.
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?
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
ok..... thanx..... i got it....