I have a table which stores all rentals and sales inventory. But because this table will be used across different reports, I have exported the who table to a QVD.
Now for one of the report I am working on, I only need the Sales Inventory Data which can be mapped by the Inventory Catalog to extract items. But I need to apply an MSSQL function to extract the purchase value on a particular day.
Here is what i am trying to do
dailyinventorystatus:
Load *,
SQL SELECT
RWReports.dbo.FindPurchasePrice(Inventorykey, TransDate , warehouse) as PurchasePrice;
Load
warehouse,
Date(Date, 'YYYY-MM-DD') as TransDate,
Inventorykey,
warehousekey,
OwnedQty,
FROM 'lib://QlikQVDs/dailyinventorystatus.qvd' (qvd)
WHERE exists (masterid, Inventorykey)
and exists (warehouseid, warehousekey);
But this clearly does not work.Can something like this be achieved?
Additionally have I used the Where exists correctly? For example I have the Inventory Table which has the masterid which can be mapped to the Inventorykey. With in the inventory table I am only extracting Sales Inventory, so in this case would the where exists work?
Secondly, how would I use the Function FindPurchaseprice from the Loaded Invetorykey, Warehouse, and Date?
