6 Replies Latest reply: May 29, 2013 6:18 AM by Gysbert Wassenaar RSS

    Join a sales row to the previous purchase row

      Hi all

       

      I have two SQL Dtabase Tables: Sales and Purchase

       

      The  Saels tables contains data about the sales and it has these columns:

       

      (columns with sample data)

      SalesID,Product,SalesPrice,Quantity,SalesDate

      ----------------------------------------------------------------------

       

      S1, Product1, 10,  10, '10/10/2010'

       

       

      The Purchase table contains data about the Purchase of products:

       

      (columns with sample data)

      PurchaseID,Product,PurchasePrice,Quantity,PurchaseDate

      --------------------------------------------------------------

       

      P9, Product1, 8,  20, '15/10/2010'

      P11, Product1, 7,  10, '09/10/2010'

      P12, Product1, 9,  20, '01/10/2010'

       

       

      in Qlikview I want to load a table which contain the Sales Lines with an extra column containing the PurchasePrice of the previous purchase.

       

       

      For example in the scenario above the result will be

       

       

      SalesID,Product,SalesPrice,Quantity,SalesDate,PurchasePrice

      ----------------------------------------------------------------------

       

      S1, Product1, 10,  10, '10/10/2010',7

       

       

      because 7 is the purchase Price of the previous purchase .

       

      Any idea?