topic Expressions : how to get previous column value in QlikView

Expressions : how to get previous column value

Wed, 01 Jun 2011 14:07:51 GMT

Hello,

I just made a pivot table.

Each line contains : an item number, a total stock quantity and then comes the "pivoted" data, a series of available stocks per date (or, to be more precise, per month).

Example:

ITEM TOTAL STOCK MONTH 0 1 2 3 4 5 6 7 8 9 10 11 12

1 1000 300 200 150 -10 -50

...

What I would like to do, with an expression, is the following :

If (Month = 0) then stock at Month 0 position = Total Stock

==> I can do it with the following expression : if (ColumnNo()=1,QTESTOCKTOTAL, ...)

But I'm stuck here :

If (value is empty) then get previous value

In the above example, I should get :

ITEM TOTAL STOCK MONTH 0 1 2 3 4 5 6 7 8 9 10 11 12

1 1000 300 300 300 200 150 150 150 -10 -10 -50 -50 -50 -50

Which expression can I use to do that ?

Thanks !

Stéphane

Re: Expressions : how to get previous column value

Wed, 01 Jun 2011 14:42:43 GMT

Hi there, I would do it within the script, in order to keep the expressions simple, like this:

Table:

crossTable('Month','Amount',2)

Load * inline

[

ITEM, TOTAL STOCK, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

1, 1000, 300, , , 200, 150, , , -10, , -50, , ,

]

;

Table2:

Load ITEM, Month, if(ITEM = previous(ITEM) and not len(Amount), peek(InventoryAmount), Amount) as InventoryAmount

resident Table

order by ITEM, Month;

drop table Table;

Hence, the expression should be somethin like this:

sum(InvetoryAmount)

Regards.

Expressions : how to get previous column value

Wed, 01 Jun 2011 14:46:37 GMT

Thanks, I will try that next week (I'm now on holidays!)

It's a pity it's not possible to do it in the Expression.

Stéphane

Re: Expressions : how to get previous column value

Wed, 01 Jun 2011 14:49:25 GMT

Stephane, You could try this with above() function.

- Sridhar

Re: Expressions : how to get previous column value

Wed, 01 Jun 2011 16:49:27 GMT

Hi all, given the location of the time dimension, the above function will not work, but you can actually try with the before function, like this:

if(len(Amount), sum(Amount), before(Inventory))

However, I think that it might be harder to identify those cases in which you actually have zero stock and those in which you do not actually have any stock entry at all. This expression will certainly work with the sample data, but you will have to take some other considerations when you have more than one stock entry, per month and item combinations.

Regards

Expressions : how to get previous column value

Mon, 06 Jun 2011 07:20:45 GMT

Thanks Ivan !

Your solution seems good. I tried your script and it's working.

I will try to adapt it to my source DB table.

Best regards and thanks again !

Stéphane