Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
The result you expect is the result I get when I join your two tables.
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
The result you expect is the result I get when I join your two tables.
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
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
];
It's true Gysbert. Thanks a lot.
Thanks Massimo. It's true. you have reason.
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.