topic Re: New field in model in QlikView

New field in model

Fri, 08 Jan 2016 19:44:27 GMT

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}

Re: New field in model

maxgro — Fri, 08 Jan 2016 20:05:32 GMT

TMP:

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

Inventory

];

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

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

Resident TMP;

Re: New field in model

Fri, 08 Jan 2016 20:13:29 GMT

Hello Max,

Thanks for the quick reply.

But the last record is being missed.

Temp1:

LOAD * INLINE [

Inventory

];

Temp2:

Load

Inventory,

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

resident Temp1;

Drop table Temp1;

Re: New field in model

maxgro — Fri, 08 Jan 2016 21:26:28 GMT

maybe this?

TMP:

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

Inventory

];

Concatenate (TMP)

first 1 load

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

Resident TMP

Order by id desc;

DROP Field id;

Re: New field in model

Fri, 08 Jan 2016 21:33:16 GMT

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

Re: New field in model

maxgro — Fri, 08 Jan 2016 22:00:15 GMT

sort by load order in sort tab

Re: New field in model

Mon, 11 Jan 2016 18:19:05 GMT

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