topic Joining tables in QlikView

Joining tables

Mon, 26 Nov 2012 08:57:10 GMT

Hello all,

How can I get the following result in QV with joining my tables?

I have 2 tables, one contains my orders, the other table has all possible items which can be bought.

Table Orders

CustomerNo	ModelNo	SerialNo	Class
00207	R15-5	AAA-1111	CE
00415	R55-7	BBB-2222	CE
....	.....	.....	....

Table Models

ModelNo	Class
R15-5	CE
R28-5	CE
R35-5	CE
R55-7	CE
R110-7	WL

My result to become when selecting customer 00207

CustomerNo	ModelClass	ModelNo	# Ordered
00207	CE	R15-5	1
00207	CE	R28-5	0
00207	CE	R35-5	0
00207	CE	R55-7	0
00207	WL	R110-7	0
Total			1

Current result

CustomerNo	ModelClass	ModelNo	# Ordered
00207	CE	R15-5	1
Total			1

I just want to see all possible options in my result as well. Even if a customer has only bought 1 Model. Anyone who can help me on this one?

Thanks in advance

Re: Joining tables

happydays1967 — Mon, 26 Nov 2012 11:40:27 GMT

The whole point is that QlikView does an automatic inner join on all fields with the same name. What you want is an outer join between some of the tables. If at all possible in Qlikview the way you want it, you should consider different fieldnames between tables and only join those of vital importance. Otherwise you should construct the query using outer joins in your source database.

Re: Joining tables

Mon, 26 Nov 2012 11:44:11 GMT

I found the solution in another thread that was posted minutes after I made myn which has the solution that I needed and I needed to uncheck the 'Suppress Zero values' in the presentation tab.

http://community.qlik.com/thread/67032?tstart=0

Thanks for the reply though.

Re: Joining tables

nirav_bhimani — Mon, 26 Nov 2012 12:09:15 GMT

HI,

Left join returns all rows from the first table, and only matching rows from the second table.

Right join returns all values from the second table, and only matching values from the first table.

Outer join returns all rows from the first table and all rows from the second table.

In your case use outer join.

try this code in script

OUTER JOIN (Models) LOAD * RESIDENT Orders;

Re: Joining tables

Wed, 28 Nov 2012 10:23:48 GMT

This didn't quit give me the result that I needed though. This was the first thing that I've tried.

Thanks for the reply though.