Find and append record

tomhovens — Tue, 23 Dec 2014 09:33:03 GMT

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?

topic Find and append record in QlikView

Find and append record