Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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.
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 residentB;
left
join
load
A;
left
join
load
B;
drop
tableA;
drop
tableB;
drop
fields a1,
b1;
load
*,if(Currency_OB<>'USD',conversion_rate*Value,Value) asAmount_Value
resident
C;
drop
tableC;