0 Replies Latest reply: Feb 16, 2016 4:54 PM by ASma Erum RSS

    Use MSSQL Function with table loaded from QVD

    ASma Erum

      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?