0 Replies Latest reply: Dec 23, 2014 4:33 AM by Marco van Zand RSS

    Find and append record

    Marco van Zand

      Dear qlikviewers

       

      I've got a script what is almost finished. It calculate the average inventory by using dates. To finalize the script I need a statement that copy the latest record, depending on:

      - Itemnr

      - Warehousenr

      - Datex

      Datex Itemnr Serialnr Warehousenr Inventoryold Mutation Inventorynew Days Daysinventory
      19-Dec-14 405015 0 1 4 -1 3 246 984
      17-Apr-14 405015 1 1 7 -3 4 0 0
      17-Apr-14 405015 0 1 4 3 7 27 108
      21-Mar-14 405015 1 1 5 -1 4 0 0
      21-Mar-14 405015 0 1 14 -9 5 1 14
      20-Mar-14 405015 1 3 0 6 6 0 0
      20-Mar-14 405015 0 1 20 -6 14 344 6880
      10-Apr-13 405015 1 6 1 -1 0 35 35
      10-Apr-13 405015 0 1 19 1 20 35 665

       

      So for a couple warehouses it needs to append the actual inventory record. As example:

       

      Datex Itemnr Serialnr Warehousenr Inventoryold Mutation Inventorynew Days Daysinventory
      23-Dec-14 405015 0 1 3 0 3 4 12

       

      It always has to be todays date in the column Datex

      The script I use

       

      vovtemp:

      SQL SELECT Aantal-mut as Mutation,
      Aantal-nieuw as Inventorynew,
      Aantal-oud as Inventoryold,
      Artikelnr as Itemnr,
      Datum as Datex,
      Magazijnnr as Warehousenr,
      Volgnummer as Serialnr
      FROM VOV_Voorraad_verslag;


      vov:

      LOAD *,
      Days * Inventoryold as Daysinventory;

      LOAD Itemnr,
      Warehousenr,
      Datex,
      Inventoryold,
      Inventorynew,
      Serialnr,
      Mutation,
      If(Itemnr=Previous(Itemnr) and Warehousenr=Previous(Warehousenr),Datex-Previous(Datex),0) as Days
      Resident vovtemp
      Order By Itemnr, Warehousenr, Datex, Serialnr;

      Drop table vovtemp;

       

       

      Who could post the script?