Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

tomhovens
New Contributor II

Find and append record

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?

Community Browser