topic Join, please help. in QlikView

Join, please help.

Wed, 24 Jun 2009 03:42:11 GMT

Hello,
I read the guide of Qlikview but I do not wnderstand the exactly difference between
simply hoi, Inner join, left hoin, rght join. Someone could to explain it by some examples?
Thank you

Best Regards.

--
Slash [:)]

Join, please help.

Wed, 24 Jun 2009 03:46:11 GMT

The Wikipedia page on SQL Joins has some good examples: http://en.wikipedia.org/wiki/Join_(SQL)

Join, please help.

johnw — Wed, 24 Jun 2009 03:56:34 GMT

I don't tend to trust just "Join", because different languages may interpret it differently. I believe that QlikView is doing an outer join by default. I always specify which type of join I want.

Inner will only keep rows if they're in BOTH tables. I typically use this to remove rows from an existing table while possibly adding additional columns.
Outer will keep all rows from both tables. I don't use this.
Left will keep all rows from the left (first) table, whether or not there's a matching row on the right (second) table. It will only keep rows from the right table if there's a matching row on the left table. I use this to add columns to an existing table when it is acceptable to have a null value for the new columns when there is no match.
Right will keep all rows from the right (second) table, whether or not there's a matching row on the left (first) table. It will only keep rows from the left table if there's a matching row on the right table. I don't use this.

Hopefully I got those right. As for examples, I'm not sure that I could improve on the examples in the help text. Just search for join, then click through inner, outer, left and right. All the examples use the same source data, so you can see exactly how they behave.

Join, please help.

Wed, 24 Jun 2009 04:38:44 GMT

yes, I understand what you wrote I tryed to build some examples by QlikView but I ecery Join (Inner, left an right ) seams the same. ..

A:
load * Inline [
cognome, nome, id
afr, aldo, 10
bafr, giovanni, 20
cafr, giacomo, 30
];

B:
Inner join (A) Load * Inline [

id, reparto
10, finanza
20, it
30, customer service
];

if I change the keyword Inner with left nothing change...

the table always joins.

Slash

Join, please help.

johnw — Wed, 24 Jun 2009 04:45:40 GMT

Both of your tables have the exact same IDs on them. There is no difference between any of the joins in that case.

Add an ID 40 to table A, and an ID 50 to table B, and you'll see the differences. Inner will only have IDs 10, 20 and 30. Outer will have 10, 20, 30, 40 and 50. Left will have 10, 20, 30 and 40. Right will have 10, 20, 30 and 50.

Join, please help.

Mon, 21 Mar 2011 06:29:29 GMT

Hi Slash,

Please find the attachment.

Hope it helps you lot.

Re: Join, please help.

Thu, 12 May 2011 07:12:50 GMT

Hi Srividhyas,

You have mentioned self-join in the document but not given its QlikView equivalent syntax.

Can you please give it?

Thanks

Samir