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?
