topic Re: Need help to join three tables in QlikView

Need help to join three tables

Anonymous — Mon, 19 Mar 2012 19:35:01 GMT

orderNo	customer	deliveryDate
1	101	2011-02-10
2	101	2012-03-11
3	102	2012-03-10
4	102	2013-04-15
5	103	2013-04-16

customer	customerGroup
101	1
102	1
103	2

customerGroup	Year	Bonus
1	2011	1,5%
1	2012	2,0%
1	2013	2,0%
2	2011	0,5%
2	2012	0,75%
2	2013	1,2%

Ok, so I have three tables that I need to JOIN and need help to figure out how to do it in QV.

Its easy enough to LEFT JOIN table2 onto table1 since they have field "customer" in common.

But then I need to LEFT JOIN table3 onto table1 using field "customerGroup" that only exists in table2 AND check that the table3.Year" = Year(table1.deliveryDate)

All fields are QUALIFIED and I create my own UNQUALIFIEDkeys between the the three tables...

Hope you understand

Re: Need help to join three tables

Mon, 19 Mar 2012 20:02:22 GMT

Here is what I did based on your examples given, making inline tables for the data above:

Table1:

LOAD * Inline [

orderNo, customer, deliveryDate

1, 101, 2011-02-10

2, 101, 2012-03-11

3, 102, 2012-03-10

4, 102, 2013-04-15

5, 103, 2013-04-16

];

left join

LOAD * Inline [

customer, customerGroup

101, 1

102, 1

103, 2

];

Table2:

LOAD *,

YEAR(deliveryDate) as deliveryYear

Resident Table1;

drop table Table1;

left join

LOAD * Inline [

customerGroup, Year, Bonus

1, 2011, .015

1, 2012, .02

1, 2013, .02

2, 2011, .005

2, 2012, .0075

2, 2013, .012

]

;

Table3:

LOAD *,

' ' as Junk

Resident Table2

WHERE deliveryYear = Year;

drop table Table2;

The resulting data set you are looking for it in Table3.

I also attached the .qvw for additional reference.

Hope this helps!