Hi, i have a situation with some data that i am not finding the right way to manipulate. I have two tables loaded: Table1: Load date, sku, stock_movement from ...
Table2: Load date, sku, new_price from ...
Table1 stores any stock movement that i have in the system, may be a positive number from a purchase to a provider or a negative number from a sell (the stock goes up or down on storage), the date is the exact date and time where that change was produced and the sku is the ID of the product (i have another table with the product description and that sku links to that)
Table2 stores when for any reason the cost of the products changes, for example, inflation adjusments on provider's cost.
the result of this tables is something like this:
DATE SKU STOCK_MOVEMENT
date#1 NNN1 +A
date#2 NNN2 -B
date#3 NNN2 -C
and
DATE SKU NEW_PRICE
date#4 NNN1 $$$1
date#5 NNN2 $$$2
date#6 NNN3 $$$3
but here is the problem, the dates are allways diferent, because is mostly impossible that i have a sale at the exact same date, hour, minute and second that i have a stock adjustment and a new price at the same time, so here is what it looks like if i make a table in qlikview
DATE SKU STOCK_MOV NEW_PRICE TOTAL
date#1 NNN1 +A -
date#2 NNN2 -B -
date#3 NNN2 +C -
date#4 NNN1 - $$$1
date#5 NNN2 - $$$2
date#6 NNN2 - $$$3
in the real data i have a lot more data and stock movements after the new prices, etc, but the problem is the same, there is no NEW_PRICE data at the same time that a STOCK_MOVEMENT, and i need to calculate a total for each row that represents the total amount of money that the stock movement represents.
I don't know if its a way to adjust the loads so that information is aligned (which will make a lot of duplicated data because i have like 20 stock movements over 1 new price) or if some combinations of Max() or MaxRange() will help.
I'd recommend transforming your pricing table into ranges of "From Date" - "To Date" with the corresponding price for each period. This can be done with the use of functions Pick() and Previous() in your data load.
Then, you can link these ranges with individual transactions using INTERVALMATCH join and that would allow you to assign the correct price to each transaction.
If you allow me to make a recommendation - since you are using QlikView, you could benefit a lot from studying my book QlikView Your Business. It describes all of these techniques and many more advanced development techniques.
thanks, i will have to work around the data because the price increments are not ordered by product sku, so i will have to work around it a little bit, but i like the approach.
