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.
