topic Maybe Outer Join in QlikView

Maybe Outer Join

Mon, 27 Jun 2011 16:34:11 GMT

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

Maybe Outer Join

jdf — Mon, 27 Jun 2011 17:41:05 GMT

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

Maybe Outer Join

Mon, 27 Jun 2011 19:46:40 GMT

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

Maybe Outer Join

janbouwmans — Mon, 27 Jun 2011 21:10:20 GMT

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

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

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

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

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

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

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

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

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

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