Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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;
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.