I am looking to get the most recent Prior Price from an excel file that varies based on MonthYear and Material.
I have a Fact table that has Invoice Dates.
The Prior Price that I fetch has to be for a date less than Invoice Date. Any clues on how to achieve this? IntervalMatch won't work because I don't have End Dates for the Excel file. FirstSortedValue() is also questionable since the Invoice Date is a non-static field.
Any thoughts on how to crack this one in script? thanks.
-- one thought is to do an OUTER JOIN on the two tables, compute the difference between the 2 dates and whichever is the least positive, use that. seems inefficient but the only way for now.
If I have understood what you need correctly I believe you have an Excel file that is a continuous list of Invoice Dates and Prices for Materials and you wish to be able to automatically pick out the latest price for each material to avoid having to search through your list. So I guess your fact table would look something like this:
Invoice Date
Price
Material
01/04/2012
10
Iron
25/05/2012
15
Iron
03/06/2012
17
Zinc
04/06/2012
20
Copper
07/07/2012
19
Iron
09/10/2012
18
Zinc
21/11/2012
25
Copper
Here is a potential solution in script.
//1:Load in your data. I've used an inline load for simplicity but this could be replaced with the load script for your Excel table.
E.g.
Load
[Invoice Date],
Price,
Material
From [filename] etc.......
So to begin load your data:
Tricky:
LOAD * INLINE [
Invoice Date, Price, Material
01/04/2012, 10,Iron
25/05/2012, 15,Iron
03/06/2012, 17,Zinc
04/06/2012, 20,Copper
07/07/2012, 19,Iron
09/10/2012, 18,Zinc
21/11/2012, 25,Copper
];
//2: Identify the total number of different price entries for each material and also the last date on which a price was recorded against each material. The Group By function does the first part when used to group the data by Material
Pricing:
Left Join (Tricky)
Load Distinct
Material,
Count(Price) as Material_Price_Count,
Date(Max([Invoice Date]),'DD/MM/YYYY') as Date_of_Prior_Price
Resident Tricky
Group By Material
;
//3: Load in the price against each material but using a where clause to limit the load to only load prices when the Invoice Date is equal to the last date on which a price was recorded against each material.
Load
Material,
Price as Prior_Price
Resident Tricky
Where [Invoice Date]=Date_of_Prior_Price;
The end result is a field called Prior_Price that holds the most recent value of Price for each material.
You can then load this into a chart or table box to display your results. For example:
Material
Material_Price_Count
Date_of_Prior_Price
Prior_Price
Copper
2
21/11/2012
25
Iron
3
07/07/2012
19
Zinc
2
09/10/2012
18
This correctly shows the latest value of Price for each material and the date on which it was valid.
I need to take an invoice for a material which could be from several vendors in the Fact table and find the MappingPrice in the Excel table which is the most recent i.e. closest to InvoiceDate but <=InvoiceDate. There can be a mapping price for every Month-Year or every so often or one a year or changes every month year.
IntervalMatch doesn’t apply because I have no end date, or that I would have to create one. Hope this helps.
