- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Multilple Left Join
Hi All,
Please consider the following scenario.
RTY:
LOAD * INLINE [
F1, F2
1, 11
2, 12
3, 13
4, 14
5, 15
6, 16
7, 17
8, 18
9, 19
10, 20
];
Left JOIN(RTY)
LOAD * INLINE [
F1, F333
1, 21
2, 22
3, 23
4, 24
5, 25
6, 26
7, 27
8, 28
9, 29
10, 30
];
Left JOIN(RTY)
LOAD * INLINE [
F1, F333
1, 31
2, 32
3, 33
4, 34
5, 35
6, 36
7, 37
8, 38
9, 39
10, 40
];
OUTPUT:
F1 | F2 | F333 |
1 | 11 | 21 |
2 | 12 | 22 |
3 | 13 | 23 |
4 | 14 | 24 |
5 | 15 | 25 |
6 | 16 | 26 |
7 | 17 | 27 |
8 | 18 | 28 |
9 | 19 | 29 |
10 | 20 | 30 |
In the following scenario only the first left join is working, second left join is not working. Please let me know why and how to resolve it.
31 - 40 is not displayed please let me know why.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you want this:
Try with this script, first loading all F333 values, and then joining the tables:
RTY:
LOAD * INLINE [
F1, F333
1, 21
2, 22
3, 23
4, 24
5, 25
6, 26
7, 27
8, 28
9, 29
10, 30
];
LOAD * INLINE [
F1, F333
1, 31
2, 32
3, 33
4, 34
5, 35
6, 36
7, 37
8, 38
9, 39
10, 40
];
Right JOIN(RTY)
LOAD * INLINE [
F1, F2
1, 11
2, 12
3, 13
4, 14
5, 15
6, 16
7, 17
8, 18
9, 19
10, 20
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi
output looks like this,
F1 | F2 | F333 | F444 |
1 | 11 | 21 | 31 |
2 | 12 | 22 | 32 |
3 | 13 | 23 | 33 |
4 | 14 | 24 | 34 |
5 | 15 | 25 | 35 |
6 | 16 | 26 | 36 |
7 | 17 | 27 | 37 |
8 | 18 | 28 | 38 |
9 | 19 | 29 | 39 |
10 | 20 | 30 | 40 |
RTY:
LOAD * INLINE [
F1, F2
1, 11
2, 12
3, 13
4, 14
5, 15
6, 16
7, 17
8, 18
9, 19
10, 20
];
Left JOIN(RTY)
LOAD * INLINE [
F1, F333
1, 21
2, 22
3, 23
4, 24
5, 25
6, 26
7, 27
8, 28
9, 29
10, 30
];
Left JOIN(RTY)
LOAD * INLINE [
F1, F444
1, 31
2, 32
3, 33
4, 34
5, 35
6, 36
7, 37
8, 38
9, 39
10, 40
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
The second join is on the combination of BOTH columns (F1 & F333) and as those data combinations don't exist at that point, won't add anything. Are you trying to add a new column or new rows?
flipside
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HII
Change the name of your field i.e F333
duplicate field you write. (Rename it And you will got answer)
and rest of your code fine......
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I wanted to new rows
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Then you can use this ...
//Left JOIN(RTY)
concatenate LOAD * INLINE [
F1, F333
1, 31
2, 32
3, 33
4, 34
5, 35
6, 36
7, 37
8, 38
9, 39
10, 40];
... but this creates no relationship between the new rows for F333 and F2 - is this expected? if not, maybe some sample data to give us context may help.
flipside
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you want this:
Try with this script, first loading all F333 values, and then joining the tables:
RTY:
LOAD * INLINE [
F1, F333
1, 21
2, 22
3, 23
4, 24
5, 25
6, 26
7, 27
8, 28
9, 29
10, 30
];
LOAD * INLINE [
F1, F333
1, 31
2, 32
3, 33
4, 34
5, 35
6, 36
7, 37
8, 38
9, 39
10, 40
];
Right JOIN(RTY)
LOAD * INLINE [
F1, F2
1, 11
2, 12
3, 13
4, 14
5, 15
6, 16
7, 17
8, 18
9, 19
10, 20
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Juan Gerardo Cabeza
I know it can be sovled as you mentioned.
I thought there would be some mistakes at my end when doing a left join at the second point.
I just wanted know to why the second left join was not working.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can u tell me the answer qv_jagan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
RTY:
LOAD * F1, F333 from xxx
LOAD * F1, F333 from xxx
RIGHT JOIN
LOAD * F1, F2 from xxx
Please check Juan Gerardo Cabeza response for further clarifications.