Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Problem with outer join

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


1P17
2P210
3P315
4P4320
5P55

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Problem with outer join

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


talk is cheap, supply exceeds demand
7 Replies

Re: Problem with outer join

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

MVP & Luminary
MVP & Luminary

Re: Problem with outer join

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


talk is cheap, supply exceeds demand
javier_florian
Contributor III

Re: Problem with outer join

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

MVP
MVP

Re: Problem with outer join

1.png

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

  ];

Not applicable

Re: Problem with outer join

It's true Gysbert. Thanks a lot.

Not applicable

Re: Problem with outer join

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

MVP
MVP

Re: Problem with outer join

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.