Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Joining tables

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

CustomerNoModelNoSerialNoClass
00207R15-5AAA-1111CE
00415R55-7BBB-2222CE
..................

Table Models

ModelNoClass
R15-5CE
R28-5CE
R35-5CE
R55-7CE
R110-7WL

My result to become when selecting customer 00207

CustomerNoModelClassModelNo# Ordered
00207CER15-51
00207CER28-50
00207CER35-50
00207CER55-70
00207WLR110-70
Total1

Current result

CustomerNoModelClassModelNo# Ordered
00207CER15-51
Total1

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

1 Solution

Accepted Solutions
Not applicable

Re: Joining tables

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.

4 Replies
happydays1967
Contributor

Re: Joining tables

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.

Not applicable

Re: Joining tables

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.

nirav_bhimani
Contributor III

Re: Joining tables

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;

Not applicable

Re: Joining tables

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.

Community Browser