Multilple Left Join

[qv_jagan]

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.

[#2]

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

];

[flipside]

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

[er_mohit]

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......

[qv_jagan]

I wanted to new rows

[flipside]

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

[#7 · Accepted solution]

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

];

[qv_jagan]

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.

[#9]

can u tell me the answer qv_jagan

[qv_jagan]

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.