Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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?

1 Solution

Accepted Solutions

Re: Join a sales row to the previous purchase row

See attached example.


talk is cheap, supply exceeds demand
6 Replies
er_mohit
Honored Contributor II

Re: Join a sales row to the previous purchase row

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

];

Not applicable

Re: Join a sales row to the previous purchase row

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

SalesID1Product1SalesPrice1Quantity1SalesDate1PurchasePrice1
S1Product1101010-10-20107
Not applicable

Re: Join a sales row to the previous purchase row

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

Not applicable

Re: Join a sales row to the previous purchase row

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
Honored Contributor II

Re: Join a sales row to the previous purchase row

On the basis of Product you can try this also

see attached file

Re: Join a sales row to the previous purchase row

See attached example.


talk is cheap, supply exceeds demand
Community Browser