Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Need help to join three tables

orderNocustomerdeliveryDate
11012011-02-10
21012012-03-11
31022012-03-10
41022013-04-15
51032013-04-16

customercustomerGroup
1011
1021
1032

customerGroupYearBonus
120111,5%
120122,0%
120132,0%
220110,5%
220120,75%
220131,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

1 Reply
Not applicable
Author

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!