0 Replies Latest reply: Apr 19, 2017 4:46 PM by Marcin Sory RSS

    left join ?

    Marcin Sory

      Hi,

      i have data:

       

      purchase (id, data, barcode, quantity)

       

      1, 2016-02-05, 11111, 200

      2, 2016-02-10, 22222, 100

      3, 2016-02-10, 33333,200

       

      sales(id, purchase_id, barcode, data, quantiy)

      1, 2, 22222, 2016-10-20, 20

      2, 2, 22222, 2016-12-30, 10

      3, 1 11111, 2016-10-31, 30

       

      I want to show stock from purchase depends to date. In sql my query will be:

      set stock_date = 2016-10-31

      select purchase.id, purchase.data, purchase.barcode, purchase.quantity as purchase, sum (x.qt) as sales 

      from purchase left outer join (

      select purchase_id, sum(quantity) as qt from sales where data <= stock_date) group by purchase_id) as x on x.purchase_id = purchase.id

       

      When I set association - it works as join. I will never see date from purchase - row no. 3

       

      How to organize data in qliksense to get effect?