topic Re: Multilple Left Join in QlikView

Multilple Left Join

qv_jagan — Fri, 25 Jan 2013 08:48:06 GMT

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.

Re: Multilple Left Join

Fri, 25 Jan 2013 08:59:26 GMT

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

];

Re: Multilple Left Join

flipside — Fri, 25 Jan 2013 08:59:53 GMT

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

Re: Multilple Left Join

er_mohit — Fri, 25 Jan 2013 09:02:15 GMT

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

Re: Multilple Left Join

qv_jagan — Fri, 25 Jan 2013 09:11:32 GMT

I wanted to new rows

Re: Multilple Left Join

flipside — Fri, 25 Jan 2013 09:15:38 GMT

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

Re: Multilple Left Join

Fri, 25 Jan 2013 09:18:35 GMT

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

];

Re: Multilple Left Join

qv_jagan — Fri, 25 Jan 2013 09:24:27 GMT

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.

Re: Multilple Left Join

Fri, 25 Jan 2013 09:27:15 GMT

can u tell me the answer qv_jagan

Re: Multilple Left Join

qv_jagan — Fri, 25 Jan 2013 09:32:06 GMT

RTY:

LOAD * F1, F333 from xxx

RIGHT JOIN

LOAD * F1, F2 from xxx

Please check Juan Gerardo Cabeza response for further clarifications.