Problem with outer join

[#1]

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

[alexandros17]

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

[Gysbert_Wassenaar · Accepted solution]

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

[javier_florian]

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

[maxgro]

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
  ];

[#6]

It's true Gysbert. Thanks a lot.

[#7]

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

[maxgro]

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.