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