Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expressions : how to get previous column value

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


5 Replies
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

- Sridhar

Not applicable
Author

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

Not applicable
Author

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