Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Joining 3 tables

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

OrderIDtransactionIDCustomerIDCompanyIDOrderdateOrderTotal
16102-2-201410
29203-2-201420
310304-2-201420
36342012-2-201425
373513012-2-201415

3 ) Codes

Codes
OrderVoucherIDCodeIDCodedateValueCodeOnlineAdditional
110 € 10,00x10
220 € 20,00y10
330 € 20,00z10
36360 € 12,50b10
37370 € 12,50c00
38380 € 15,00d11

The wanted output must be:

OrderIDOrderVoucherIDCustomerIDOrderdateCodedateOrderTotalValueCodeOnlineAdditional
1112-2-2014010 € 10,00x10
2223-2-2014020 € 20,00y10
3334-2-2014020 € 20,00z10
3636212-2-2014025 € 12,50b10
3637212-2-2014025 € 12,50c00
37381312-2-2014015 € 15,00d11

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

2 Replies
Highlighted
Not applicable

Re: Joining 3 tables

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 ...

Highlighted
Not applicable

Re: Joining 3 tables

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.