Join Problem

[#1]

Hi,

Now i am facing the problem about the "Join".

After i join the records are become huge.

I used the left join, and i used one field as key field.

The sample code is follow.

test1:

Load

ABC, DEF,GHI

from abcd.txt;

tab1:

Load

aa, bb, cc

from xyz.txt;

left join

load

ABC as aa,

DEF,GHI

resident test1;

Let say, when the original records at xyz.txt has only 5558 records(row).

After i join, the total records become 56608 records(row).

Thanks alot

kzorrw

[eiconsulting]

Beside the fact that you should drop test1 once you have joined everything in tab1 ... why don't you make it shorter like this and see what happens?

<code\>

tab1:

Load
aa,
bb,
cc
from xyz.txt;

LEFT JOIN Load
ABC as aa,
DEF,
GHI
from abcd.txt;

<\code>

[eiconsulting]

Beside the fact that you should drop test1 once you have joined everything in tab1 ... why don't you make it shorter like this and see what happens?

Flavio

[code\]

tab1:

Load
aa,
bb,
cc
from xyz.txt;

LEFT JOIN Load
ABC as aa,
DEF,
GHI
from abcd.txt;

[\code]

[#4]

Hi Flavio,

I tried it. Stil the same, A lot of records came out.

I dropped the table also.

Thanks,

kzorrw

[eiconsulting]

The LEFT join does not limit the # of records to the rows of the first table... to calculate how many there should be in the result you should also know how many there in the second table and most of all how many records match the key.

I made a test table1 has 5 records and table2 has 10 (of which one gets eliminated because the key is not in the main table). The result has all the 5 records multiplied by how many different lines for the same key there were, in this case I put just 3 on just 3 items so the result was 3*3+2=11 - so ALL THE RECORDS of the second table that match + THE RECORDS THAT ARE UNIQUE to the main table . Try to verify your issue on a smaller sample.

LOAD * INLINE [
aa,F2
1,a
2,b
3,c
4,d
5,e
];

LEFT JOIN LOAD * INLINE [
aa,F3
1,aa
2,aa
3,aa
1,bb
2,bb
3,bb
1,cc
2,cc
3,cc
6,dd
];

aa F2 F3
1 a aa
1 a bb
1 a cc
2 b aa
2 b bb
2 b cc
3 c aa
3 c bb
3 c cc
4 d
5 e

[agrisyst]

Hello kzorrw ,

Looking to find a solution for a similar problem I found your question in the forum. Did you solve the issue? Do you know hwat caused it and how to get rid of it....?

I am facing a similar problem so would be very happy to get a solution...

Regards

Marc

[amars]

test1:

Load
ABC, DEF,GHI, ABC as aa
from abcd.txt;

Left join
Load
aa, bb, cc
from xyz.txt;

[Oleg_Troyansky]

Based on your description, I can only see two possibilities:

1. Either your second table is so much more detailed than the first one, and you are truly getting many times more rows

2. Or you misspelled your common key (one you call here "aa"). I suppose your real code has better field names than those - compare them carefully, and keep in mind 0 they are case sensitive. If your "join key" is misspelled, QlikView is generating a "Cartesian multiplication" - matching every row from one table to every row from the other table. Based on your reaction on the size, I'm guessing that this is what's happening.

[agrisyst]

Hi Amars,

Thanks for trying to help me but unfortunately is it not that simple...

I think I know where to look but as a newbie do not know yet where to find the solution...

The problem is that for a table which I load RESIDENT (as a matter of fact also when I load it as a QVD after I stored it into a qvd file), the table is much larger then the actual records I assumed there should be.... If I export the same records into a excel and then load the excel in the JOIN statement it works well....

One way are the other it seems that in the second table there are more records (not visible etc.) then there should be....

I hope this makes a little bit clear what I am strugling with....

Marc