New field in model

[#1]

Hi,

I'm new to QlikView and I have small requirement which I'm unable to do it.

I want to create a new field in my model called as Previous Inventory.

Which should have the first row as zero and all the other rows should have the same records as the inventory field.

Inventory={23,25,29,37,70}

Previous Inventory={0,23,25,29,37,70}

[maxgro]

TMP:

LOAD *, alt(Peek('Inventory'), 0) as [Previous Inventory] INLINE [

    Inventory

    23

    25

    27

    29

    30

];

or if you want a field with all the values (NewField), add to previous code

load Concat([Previous Inventory], ',') as NewField

Resident TMP;

[#3]

Hello Max,

Thanks for the quick reply.

But the last record is being missed.

Temp1:

LOAD * INLINE [

    Inventory

    23

    25

    29

    37

    70

];

Temp2:

Load

Inventory,

alt(Peek('Inventory'),0) as [Previous Inventory]

resident Temp1;

Drop table Temp1;

[maxgro]

maybe this?

TMP:

LOAD rowno() as id, *, alt(Peek('Inventory'), 0) as [Previous Inventory] INLINE [

    Inventory

    23

    25

    29

    37

    70

];

Concatenate (TMP)

first 1 load

'?' as Inventory, Inventory as [Previous Inventory]

Resident TMP

Order by id desc;

DROP Field id; 

[#5]

The image is what I want.

But the script returns '?' as the first record in Inventory.

Is there a way to push it at the end

[maxgro]

sort by load order in sort tab

[#7]

Hi Max,

The problem I'm facing here is, after creating the previous inventory field, I need to do the average of inventory and previous inventory.

A sort on the front end won't give me accurate values.

Hope I'm precise in explaining the requirement/issue.

Regards,

Adi