topic Re: Problem with outer join in QlikView

Problem with outer join

Thu, 26 Jun 2014 13:40:33 GMT

Hi,

excuse for my english,

I have a doubt about the use of OUTER JOIN:

My script is this:

Table_OuterJoin1:

LOAD * INLINE [

PK, Product, invent_sum

1, 'P1', 5

2, 'P2', 10

3, 'P3', 15

4, 'P4', 20

1, 'P1', 34

];

Tabla_OuterJoin2:

LOAD * INLINE [

PK, Product, ordered

1, 'P1', 7

4, 'P4', 3

5, 'P5', 5

];

OUTER JOIN(Tabla_OuterJoin1)

LOAD * RESIDENT Tabla_OuterJoin2;

The result of this script isn't as I expected:

PK product ordered invent_sum

1	P1	7
2	P2		10
3	P3		15
4	P4	3	20
5	P5	5

I expected two rows for the PK=1

1 P1 7 5

1 P1 7 34

How do I gain this result?

Thanks in advance

Re: Problem with outer join

alexandros17 — Thu, 26 Jun 2014 13:45:13 GMT

Outer Join returns all the combinations from loaded records, you need an INNER JOIN.

Finally remember to drop Table_OuterJoin1 in thid way:

DROP TABLE Table_OuterJoin1;

Let me know

Re: Problem with outer join

Gysbert_Wassenaar — Thu, 26 Jun 2014 13:48:17 GMT

The result you expect is the result I get when I join your two tables.

Re: Problem with outer join

javier_florian — Thu, 26 Jun 2014 13:51:06 GMT

Hi Javier,

Some cases you need outer join to get all possible values, so try with:

Table_OuterJoin1:

LOAD * INLINE [

PK, Product, invent_sum

1, 'P1', 5

2, 'P2', 10

3, 'P3', 15

4, 'P4', 20

1, 'P1', 34

];

OUTER JOIN

LOAD * INLINE [

PK, Product, ordered

1, 'P1', 7

4, 'P4', 3

5, 'P5', 5

];

-JFlorian

Re: Problem with outer join

maxgro — Thu, 26 Jun 2014 16:50:11 GMT

Table_OuterJoin1:

LOAD * INLINE [

PK, Product, invent_sum

1, 'P1', 5

2, 'P2', 10

3, 'P3', 15

4, 'P4', 20

1, 'P1', 34

];

outer join (Table_OuterJoin1) // you can remove outer

// Tabla_OuterJoin2:

LOAD * INLINE [

PK, Product, ordered

1, 'P1', 7

4, 'P4', 3

5, 'P5', 5

];

Re: Problem with outer join

Tue, 01 Jul 2014 07:39:25 GMT

It's true Gysbert. Thanks a lot.

Re: Problem with outer join

Tue, 01 Jul 2014 07:40:36 GMT

Thanks Massimo. It's true. you have reason.

Re: Problem with outer join

maxgro — Tue, 01 Jul 2014 07:45:42 GMT

When you think you get the answer, please close this discussion by giving Correct and Helpful answers to the posts which are useful for you. It helps others in finding answers for similar scenarios. Thanks and good Qlik.