topic Re: qlikview join vs sql joins in QlikView

qlikview join vs sql joins

Fri, 19 Dec 2014 10:20:11 GMT

Hello,

I have a question!

I have in qlikview a tempbase which is first joined on my big table - what is join similar to in SQL - is it a inner join?

example:

Table1:

Row

Row2

Row3

Row4

Row4+Row3 as Row_34

from xxxx

table_tmp:

load

Row

Row2

Row_34

resident table1;

join(table1)

load

Row_34

max(row) as RowMax

resident table_tmp

So what does the join means? is it a left or inner join?

Re: qlikview join vs sql joins

sunilkumarqv — Fri, 19 Dec 2014 10:26:18 GMT

Hi thomas ,

in qlikview join becomes outer join .

Understanding Join, Keep and Concatenate

j.denard May 10, 2012 5:29 PM

We have implemented the QlikView Join, Keep and Concatenate in QvScriptor, a new revolutionary tool, able to generate QlikView script from visual schema.

We have written this small doc for the help file and we thing interesting to share it with the community as we found only partial explanations about the methods.

The QlikView script functions JOIN, KEEP and CONCATENATE can sometimes be used to solve 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 and that Table2 has a key 4 that is not present in Table1.

Table1		Table2
Key	A	Key	C
1	A1	1	C1
2	A2	2	C2
3	A3
		4	C4

Type of Joins:

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.
The keyword LEFT will retain rows from the left table but only matching rows from the right table
The keyword RIGHT will retain rows from the right table but only matching rows from the left table
The keyword INNER will retain rows matching the left table and right table

Joins

Here's what the merged table will look like after the different join methods.

Note that QlikView merge the two tables after a Join.

Note: The explicit join keyword (= Outer Join) in QlikView script language performs a full join of the two tables. The result is one table. In many cases such joins will result in very large tables. One of the main features of QlikView is its ability to make associations between tables instead of joining them, which greatly reduces memory usage, increases processing speed and offers enormous flexibility. Explicit joins should therefore be generally avoided in QlikView scripts. The keep functionality was designed to reduce the number of cases where you need to use explicit joins

OUTER JOIN (Table1)			LEFT JOIN (Table1)
Key	A	C	Key	A	C
1	A1	C1	1	A1	C1
2	A2	C2	2	A2	C2
3	A3	-	3	A3	-
4	-	C4

RIGHT JOIN (Table1)			INNER JOIN (Table1)
Key	A	C	Key	A	C
1	A1	C1	1	A1	C1
2	A2	C2	2	A2	C2

4	-	C4

Re: qlikview join vs sql joins

anbu1984 — Fri, 19 Dec 2014 10:26:30 GMT

Default is full outer join

For Inner, use Inner Join

For Left, use Left Join

Edit:

Table1:

Load * Inline [

Key,Data1

1,aa

2,bb ];

Join(Table1)

Load * Inline [

Key,Data2

1,aa

3,cc ];

Re: qlikview join vs sql joins

Anonymous — Fri, 19 Dec 2014 10:26:41 GMT

Its inner join here. ( as SQL)

Re: qlikview join vs sql joins

Fri, 19 Dec 2014 10:30:48 GMT

Useful document about joins

Re: qlikview join vs sql joins

Fri, 19 Dec 2014 12:41:31 GMT

the document says its a outer join

Re: qlikview join vs sql joins

Fri, 19 Dec 2014 12:42:00 GMT

so an ordinary join is a full outer join?!

Re: qlikview join vs sql joins

anbu1984 — Fri, 19 Dec 2014 12:46:08 GMT

Yes. Run the above script and check the result

Re: qlikview join vs sql joins

Anonymous — Fri, 19 Dec 2014 12:54:20 GMT

Hi,

If Only Join is mentioned its full outer join.

Regards

Neetha

Re: qlikview join vs sql joins

Anonymous — Fri, 19 Dec 2014 12:59:06 GMT

hi ,

See below post:

http://community.qlik.com/docs/DOC-7457

Re: qlikview join vs sql joins

its_anandrjs — Fri, 19 Dec 2014 16:42:55 GMT

Hi,

Check some of this for better Joins explains how it works and what result you get after the joins.

Joins and Lookups

Some Qlikview Joins best documents

Different Join Functions in Qlikview

Regards

Anand