Join a sales row to the previous purchase row

[#1]

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?

[er_mohit]

See the attached file

Try this

Purchase:

LOAD * Inline [

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

];

Temp:

Mapping LOAD

Quantity,PurchasePrice

Resident Purchase;

DROP Table Purchase;

Sales:

LOAD ApplyMap('Temp',Quantity) as PurchasePrice,* Inline [

SalesID,Product,SalesPrice,Quantity,SalesDate

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

];

[#3]

sales:

LOAD *,date(DATE#(SalesDate,'DD/MM/YYYY'),'DD-MM-YYYY') as SalesDate_new  INLINE [

    SalesID, Product, SalesPrice, Quantity, SalesDate

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

];

join

Purchase:

LOAD *,date(DATE#(PurchaseDate,'DD/MM/YYYY'),'DD-MM-YYYY') as PurchaseDate_new INLINE [

    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

];

result:

load

SalesID as SalesID1,

Product as Product1,

SalesPrice as SalesPrice1,

Quantity as Quantity1,

SalesDate_new as SalesDate1,

PurchasePrice as PurchasePrice1,

PurchaseDate_new as PurchaseDate1

Resident sales

where num(date(SalesDate_new))= num(date(PurchaseDate_new +1));

drop Table sales;

THEN OUTPUT LIKE THIS

SalesID1	Product1	SalesPrice1	Quantity1	SalesDate1	PurchasePrice1
S1	Product1	10	10	10-10-2010	7

[#4]

it's not necessaryt that the previous date of a purchase have one day difference , it was just an example

[#5]

then simply use LEFT JOIN between two table where first load sales and then load Purchase like this

sales:

LOAD *,date(DATE#(SalesDate,'DD/MM/YYYY'),'DD-MM-YYYY') as SalesDate_new  INLINE [

    SalesID, Product, SalesPrice, Quantity, SalesDate

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

];

left join

Purchase:

LOAD *,date(DATE#(PurchaseDate,'DD/MM/YYYY'),'DD-MM-YYYY') as PurchaseDate_new INLINE [

    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

];

then output like this

[er_mohit]

On the basis of Product you can try this also

see attached file

[Gysbert_Wassenaar · Accepted solution]

See attached example.