Multiple prices depending on the price lists

[#1]

Hello,

I have an invoice table containing : InvoiceDate, InvoiceNumber, ProductNumber, ProductName, SalePrice, Quantity, UM. In this table, for every invoice i can calculate the Netvalue like this: sum(Quantity * Saleprice) grouped by InvoiceNumber and InvoiceDate.

The ProductNumber is unique in my database.

I have another table containing the price list for every product in time. The table is containing : ProductNumber, PriceList(multiple for one ProductNumber), StartDate for the price list, EndDate for the price list. Example for one Product:Ex.1

For calculating the GrossValue of an invoice, i have to do: sum(Quantity * PriceList), but i need to get the PriceList for that InvoiceDate. What is the right price , how to do this in qlikview script?

Please help me to solve this.

Thank you very much in advance.

Ex.1:

ProductNumber	PriceList	StartDate	EndDate
0DALDLMFTRPS400	6.05	1/1/2008	3/16/2008
0DALDLMFTRPS400	7.05	3/17/2008	12/31/2008
0DALDLMFTRPS400	6.30	1/1/2009	2/8/2009
0DALDLMFTRPS400	6.75	2/9/2009	4/30/2009
0DALDLMFTRPS400	6.55	5/1/2009	1/31/2010
0DALDLMFTRPS400	6.09	2/1/2010	7/31/2010
0DALDLMFTRPS400	6.20	8/1/2010	12/31/9999

[#2]

Hi, Find the attached application which may suit ur need.

This application is for currency conversion where currency rate will be fluctuating like ur product price list

in the intervalmatch function use ur invoicedate and

in place of Effective_start_date user ur start date

in place of effective_End_Date user ur End date

[#3]

Thank you very much but i can't open the file. I am using due to company police qlikview8.01.

Please help me and tell what i have to do.

[#4]

Hi this is sample Code for currency conversion

A:

LOAD

Currency_OB

,

Order_Date_OB

,

Value

FROM

[Order Booking Table.xls]

(

biff, embedded labels, table is Sheet1$)

;

B:

LOAD

effective_start_date

,

effective_end_date

,

conversion_rate

FROM

[Currency Conversion Tbale.xls]

(

biff, embedded labels, table is Sheet1$) where year(effective_end_date)<=year(today())+11

;

C:

intervalmatch

( Order_Date_OB) load effective_start_date,effective_end_date resident

B;

left

join

load

*,'' as a1 resident

A;

left

join

load

*,'' as b1 resident

B;

drop

table

A;

drop

table

B;

drop

fields a1,

b1;

load

*,if(Currency_OB<>'USD',conversion_rate*Value,Value) as

Amount_Value

resident

C;

drop

table

C;