Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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.