Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
I've been busy with joining and concatenating, but I haven't found a solution yet.
Hopefully you can help me out.
1) connecting table "OrderVouchers"
OrderID OrderVoucherID Amount
1 1 10
2 2 20
3 3 20
36 36 12,5
36 37 12,5
37 38 15
2) Orders
OrderID | transactionID | CustomerID | CompanyID | Orderdate | OrderTotal |
1 | 6 | 1 | 0 | 2-2-2014 | 10 |
2 | 9 | 2 | 0 | 3-2-2014 | 20 |
3 | 10 | 3 | 0 | 4-2-2014 | 20 |
36 | 34 | 2 | 0 | 12-2-2014 | 25 |
37 | 35 | 13 | 0 | 12-2-2014 | 15 |
3 ) Codes
Codes | ||||||
OrderVoucherID | CodeID | Codedate | Value | Code | Online | Additional |
1 | 1 | 0 | € 10,00 | x | 1 | 0 |
2 | 2 | 0 | € 20,00 | y | 1 | 0 |
3 | 3 | 0 | € 20,00 | z | 1 | 0 |
36 | 36 | 0 | € 12,50 | b | 1 | 0 |
37 | 37 | 0 | € 12,50 | c | 0 | 0 |
38 | 38 | 0 | € 15,00 | d | 1 | 1 |
The wanted output must be:
OrderID | OrderVoucherID | CustomerID | Orderdate | Codedate | OrderTotal | Value | Code | Online | Additional |
1 | 1 | 1 | 2-2-2014 | 0 | 10 | € 10,00 | x | 1 | 0 |
2 | 2 | 2 | 3-2-2014 | 0 | 20 | € 20,00 | y | 1 | 0 |
3 | 3 | 3 | 4-2-2014 | 0 | 20 | € 20,00 | z | 1 | 0 |
36 | 36 | 2 | 12-2-2014 | 0 | 25 | € 12,50 | b | 1 | 0 |
36 | 37 | 2 | 12-2-2014 | 0 | 25 | € 12,50 | c | 0 | 0 |
37 | 38 | 13 | 12-2-2014 | 0 | 15 | € 15,00 | d | 1 | 1 |
So 1 order can have multiple vouchers.
It would be perfect to join the columns Orderdate and Codedate, because when one is filled with a date the other isn't, and visa versa.
Hope my question is clear. If there are any questions, let me know.
Kind regards,
Erwin
As far as I can tell you can do the following to get the exact same output table as per above
OrderVouchers:
LOAD
OrderID,
OrderVoucherID
From ....
LEFT JOIN (OrderVouchers)
Orders:
LOAD
OrderID,
CustomerID,
Orderdate,
OrderTotal
From ...
LEFT JOIN (OrderVouchers)
Codes:
LOAD
OrderVoucherID,
CodeDate,
Value,
Code,
Online,
Additional
From ...
Hi Erwin, Just join the tables on the right keys like below:
OrderDetails:
LOAD
OrderID,
OrderVoucherID
From OrderVouchers ;
LEFT JOIN (OrderVouchers)
LOAD
OrderID,
CustomerID,
Orderdate,
OrderTotal
From Orders;
LEFT JOIN (OrderDetails)
LOAD
OrderVoucherID,
CodeDate,
Value,
Code,
Online,
Additional
From Codes ;
Please find the attached qvw for reference.