Maybe Outer Join

[#1]

IDear I'm getting crazy probably for nothing... Here I've my problem... :

I've 2 tables. I can't use Left or right join 'cause the whole record could come from both tables .

I can't use the outer join 'cause I've to add record to the first table (missing) for every PO. So Mainitem is repeaing every PO and I need to have the MainItem+Component possible combination for every PO

PO	MaintItem	Component
1	110	a1
1	110	a2
1	110	a3
1	110	a4
1	110	a5
1	110	a7
1	110	a8
2	110	a1
2	110	a2
2	110	a3
2	110	a4
2	110	a5
2	110	a6

MaintItem	Component
110	a1
110	a2
110	a3
110	a4
110	a5
110	a6
110	a8
110	a9

What I want is :

PO	MaintItem	Component
1	110	a1
1	110	a2
1	110	a3
1	110	a4
1	110	a5
	110	a6
1	110	a7
1	110	a8
	110	a9
2	110	a1
2	110	a2
2	110	a3
2	110	a4
2	110	a5
2	110	a6
	110	a8
	110	a9

It seems easy but I don't have enough confidence to get it out.

Thanks for any help !

Nil

[jdf]

Nil,

You are correct this is pretty easy.  You can just use a "JOIN".  Most people think the Join fuction defaults to an inner join, as it does in SQL.  But in QlikView a join will create an outer join with the table previously created and join the tables on common field names.  If you have ever used Venn diagrams, this would be a full union between the two tables.  For everytime the values are different in the common fields, the new table will create a new record.  In your example you would want to make sure the MaintItem and Component field were labeled the same.  Your script would look like this:

tablename:

Load

PO,

MaintItem,

Component
from [Data Source Table 1];

JOIN

Load

MaintItem,

Component
from [Data Source Table 2];

The tablename will be the table you have described as your desired output.  Let me know if you have any questions.

Thanks,

Jacob

[#3]

Hi Jackob,

What I want is a full MainItem & Component combination for every PO so a join don't get the result.

In addition I've seen that the table I saved in the example above didn't have all the PO values, but it doesn't change too much.

Here is :

PO	MaintItem	Component
1	110	a1
1	110	a2
1	110	a3
1	110	a4
1	110	a5
1	110	a6
1	110	a7
1	110	a8
1	110	a9
2	110	a1
2	110	a2
2	110	a3
2	110	a4
2	110	a5
2	110	a6
2	110	a8
2	110	a9

I've done but I think it could be done in a better way. Here u are :

Combo :

Load

MaintItem,

Component

from [Data Source Table 1];

OUTER JOIN LOAD MainItem,

     Component

from [Data Source Table 2];

left join LOAD

     NrPo ,

     MainItem

from [Data Source Table 1];

(for sure u can save the previous table and use a resident in the second step).

Right ? Any other idea ?

Nil

[janbouwmans]

Hi Nil,

If you want to have a full join of every combination , you first have to create a table with every possible combination:

Tmp_MainitemList:

Load

PO from [DataSource Table1];

Join load Distinct

MainItem,

Component

from [DataSouce Table 2];

This would give you every possible combination for PO, and (Component and MainItem) as a Generic Backbone.

Now you can attach the data from DataSource Table 1 again:

load

PO,

MainItem

From [DataSource Table 1];

Hope this helps